Google Sheets – How to Use INDIRECT to Create Dynamic Length Range

formulasgoogle sheets

Part 1

To create a dynamic length range I think I should be using INDIRECT() in some way but just can't quite get my head around how.

How can I use the function counta() as the end of each column's chosen range?

Part 2

I'm using Google Sheets but I suspect the answer may be the same for Excel as well.

I have two columns full of string data that are dynamically pulled from a website, therefore I can't know for certain how long the columns will be. I wish to combine them into a single column, so have been using

={ARRAYFORMULA(S2:S100);arrayformula(AI2:AI100)}

It works but of course I end up with a whole bunch of blanks between the two merged columns, since I am combining each column up to row 100 when there is only data for maybe 50 or so rows each.

I would like to specify the ending of each range, to be the count of that range. Something like this (which is of course totally improper syntax):

={ARRAYFORMULA(S2:Scounta(S:S));arrayformula(AI2:AIcounta(ai:ai)}

That's why I need the solution for Part 1.

Best Answer

Part 1

How can I use the function counta() as the end of each column's chosen range?

Use CONCATENATE, CONCAT or & operator.

As you already figured out, to create a dynamic reference use INDIRECT. Example:

=INDIRECT("S2:S"&COUNTA(S2:S))

Part 2

The final formula is

={INDIRECT("S2:S"&COUNTA(S2:S));INDIRECT("AI2:AI"&COUNTA(AI2:AI))}

An alternative to get the same result is to use FILTER. The advantage is that it will work for strings and numbers. Example:

={FILTER(S2:S,LEN(S2:S);FILTER(AI2:AI,LEN(AI2:AI)}

Another alternative is to use QUERY. The advantage of it is that the import formulas could be combined into an array an use it of the data argument of QUERY:

=QUERY({import1;import2},"select Col1 where Col1<>'' and Col1<>'Header'")

References

Related Topic