17

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

Three level hierarchical data

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

Level 1 Data Level 2 Data

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.

5 Answers 5

25

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..

3
  • 2
    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
3

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

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

;)

2
  • 2
    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.
    – Allan
    Jan 31, 2020 at 16:23
  • 1
    @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
    – AndriuZ
    Dec 8, 2021 at 15:34
1

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.

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

TOCOL Function

=TOCOL(Sheet1!A:A,1)
  • May be the shortest approach.
  • TOCOL removes blanks when ignore=1
  • Added to Sheets Feb/2023.

Syntax

=TOCOL(array_or_range,
       [ignore],
       [scan_by_col])
-2
=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)))

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.