1

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.

  • I thought I had found a solution in another question, but it turns out that it doesn't work because while it does merge the columns, it skips blanks which are critical in the result. Does anyone have any idea how to apply an arrayformula to a dynamic range? – Jonathan van Clute Aug 27 '16 at 17:55
  • Related: webapps.stackexchange.com/questions/97587/… – Rubén Aug 27 '16 at 18:23
  • 1
    Possible duplicate of Concatenate several columns into one in Google Sheets – Rubén Aug 27 '16 at 18:26
  • Not a duplicate because I need to include blanks in the result. That solution clearly focuses on non-blank cells. – Jonathan van Clute Aug 27 '16 at 18:27
  • Thought I had it after modifying the solution in that link, but unfortunately it's still a no-go. I need to be able to define the length of the arrayformula range dynamically. – Jonathan van Clute Aug 27 '16 at 19:02
3

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

  • Ah I was so close with indirect! This finally got it... thanks! – Jonathan van Clute Aug 28 '16 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.