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


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):


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?
    – JVC
    Commented Aug 27, 2016 at 17:55
  • Related: webapps.stackexchange.com/questions/97587/… Commented Aug 27, 2016 at 18:23
  • 1
    Possible duplicate of Concatenate several columns into one in Google Sheets Commented Aug 27, 2016 at 18:26
  • Not a duplicate because I need to include blanks in the result. That solution clearly focuses on non-blank cells.
    – JVC
    Commented Aug 27, 2016 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.
    – JVC
    Commented Aug 27, 2016 at 19:02

1 Answer 1


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:


Part 2

The final formula is


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


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'")


  • Ah I was so close with indirect! This finally got it... thanks!
    – JVC
    Commented Aug 28, 2016 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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