2

Here is the same question and its answer for Excel. I want this method for Google Sheets.

https://superuser.com/questions/1623756/how-can-i-stack-all-non-blank-cells-from-a-column/1830491#1830491

I am looking for a formula that will take all non-blank values in one column and stack them in another column.

Please see example below. Essentially I am trying to go from column A to B using a formula:

A B
1 Apple Apple
2 Orange
3 Orange Lemon
4 Lemon Pear
5 Banana
6
7 Pear
8
9 Banana
I have tried the following formula, but it seems to only replicate column A.

=IFERROR(INDEX($A$1:$A$9,SMALL(IF($A$1:$A$9<>"",ROW($A$1:$A$9)-ROW($A$1)+1),ROWS($A$1:$A1))),"")

Any ideas on how to tackle this?

Thank you.

0

1 Answer 1

3

Using TOCOL() function in Google Sheets

enter image description here


=TOCOL(A1:A,1)

Or, its better to use FILTER() function:

=IFNA(FILTER(A1:A,A1:A<>""),"")

Edit:

As suggested by Blindspots Sir, few short alternative versions, which takes care of the errors as well, when there is no data, and will return #REF! error using TOCOL() therefore using the following which will not occur:

Using TOCOL()

={TOCOL(A:A,1);IFNA(#N/A)}

enter image description here


Or, Using with FILTER()

=IFNA(FILTER(A:A,LEN(A:A)),)

13
  • 1
    Sir, I am not familiar and well versed with Google Sheets, also, I found while using TOCOL() if there is no reference and using IFERROR() it was still showing #REF! error but using FILTER() it wasnt so I have said. Commented Feb 17 at 9:15
  • 1
    @Blindspots the answer has a point. When the column is blank, tocol(A1:A, 1) will error out, and that error cannot be caught the normal way. To catch the error, you need to use filter() or friends. In my opinion, A1:A is preferable to A:A, because that way nothing will break if you later insert a header row. Commented Feb 17 at 13:41
  • 1
    @Blindspots Sir, no it will still return #REF! error . Here is a screenshot , I came across this problem while giving a solution in Stackoverflow, for Excel tag, where upon OP, after accepting the answer said the formula is not working and they are using Google Sheets. Even wrapping within IFERROR() it doesn't go away. it still exisit. Commented Feb 17 at 20:47
  • 1
    You are correct. I tested =TOCOL(A:A, 2) successfully didnt expect =TOCOL(A:A, 3) to reintroduce it. So, =TOCOL(A:A, 1) isn't a good option if there may be absolutely no values in the column and a #REF! error is undesirable. I find the behavior intriguing and will look into it further. =IFNA(FILTER(A:A,A:A<>""),) is then pretty short. I did find the following works as well and shaves a few more characters off and makes it more peculuar: ={TOCOL(A:A,1);IFNA(#N/A)}
    – Blindspots
    Commented Feb 17 at 22:18
  • 2
    @Blindspots re "making a statement about what is more efficient" — for the record, filter(A1:A, A1:A <> "") is about twice as fast as tocol(A1:A, 1), and filter(A1:A, isnumber(A1:A)) is another 50% faster still. Be aware that {TOCOL(A:A,1);IFNA(#N/A)} has a side-effect: it appends a blank value at the bottom. The question specifies "take all non-blank values" so the formula should not be adding one. The reason why null arrays throw a reference error is that Google Sheet formulas are expected to return either a single value or a 2D array, and the concept of [[]] is not supported. Commented Feb 17 at 23:20

Your Answer

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