# How to skip blank cells when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheets that looks something like this:

As data is being entered in here, I want it collect in three separate sheets like this:

The solution I am looking for is a formula driven approach. I have tried VLOOKUP but can't seem to figure out how to make it work for this situation. I have googled quite a bit and all I am getting is applying filters, or removing blank cells like this one, but that does not serve my purpose.

Filter() should do that. Let's say the sheet with the 'source data' is called Sheet1, then on the second sheet (where you want the level1 names), in cell A1 try:

``````=filter(Sheet1!A:A; len(Sheet1!A:A))
``````

and repeat for all the other sheets and ranges..

• Thanks for pointing me to the FILTER function. Exactly what was needed. But to achieve what I wanted, I made one variation - FILTER(A2:B10, NOT(ISBLANK(A2:A10))) that checks for Non-blank cells. Thanks a lot @JPV! Jan 10, 2015 at 12:35
• you are welcome, Moiz. The formula I provided does exactly that. It filters the range to show only those cells that have a certain length (and thus are not blank).
– JPV
Jan 10, 2015 at 13:07
• @JVP, you are right, your formula also works. Thanks! Jan 14, 2015 at 12:01

(if you still need that - probably is the simplest way would be:

``````UNIQUE(Sheet1!A:A)` or `SORT(Sheet1!A:A;condition)
``````

;)

• The problem that I've faced with UNIQUE is that it still filters in a blank row if there is one in the range. I find FILTER to work better. Jan 31, 2020 at 16:23
• @Allan agree, but probably bigger problem with UNIQUE() is that it filters all the same values and give you only 1 record in the separate sheet Dec 8, 2021 at 15:34

Another way is to use `QUERY()`, an advanced feature, eg;

``````=QUERY(Sheet2!A:A, "select * where Col1 is not null and Col1 != ''", 0)
``````

Note that in some cases you won't need `and Col1 != ''` which filters out empty text, as opposed to completely empty cells.

You can use this feature to combine many ranges into one, for example this would "stack" all data from three separate sheets (named March, April, and June) with empty cells filtered out:

``````=QUERY(
{March!A1:Z; April!A2:Z; June!A2:Z},
"select * where Col1 is not null and Col1 != ''",
0)
``````

(In this case, we preserve the header cell from the first sheet, and skip it in the ensuing sheets).

You can learn more about the `{}` syntax for combining ranges here.

• thanks for pointing to the array concept. It was new to me. Jul 17, 2021 at 5:24

# TOCOL Function

``````=TOCOL(Sheet1!A:A,1)
``````
• May be the shortest approach.
• TOCOL removes blanks when `ignore=1`

# Syntax

``````=TOCOL(array_or_range,
[ignore],
[scan_by_col])
``````
``````=flatten(filter(A:A;not(isblank(A:A))))
``````

or

``````=flatten(filter(A:A;len(A:A)))
``````

or

``````=filter(flatten(A:A);len(flatten(A:A)))
``````