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

26

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

0
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
    Commented 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
    Commented Dec 8, 2021 at 15:34
2

You can also use the QUERY function.* see important notes

SYNTAX: QUERY(data, query, [headers])

For example:

=QUERY(A:A, "where A is not null", 0)

Equivalent variations (not exhaustive):

=QUERY(A:A, "select A where A is not null", 0)
=QUERY(A:A, "select * where A is not null", 0)

Specifying Columns (Index vs. Letter)

QUERY supports the following options for specifying columns.

  1. By Letter - only supported where data is a range.
    Typically produces shorter and more readable formulas
    =QUERY(K:N, "select L")
    
  2. By Index - This is supported for both ranges and arrays
    =QUERY(K:N, "select Col2")        //  Column L is INDEX(K:N,,2)
    =QUERY({K:L,M:N}, "select Col2")  //  Column L is INDEX({K:L,M:N},,2)
    
    

Arrays vs. Ranges

Because QUERY supports both arrays and ranges, you can combine ranges directly in the data argument.

The following example:

  1. Creates an array for the data argument from Columns A to Z of three different sheets
  2. Removes rows where Column A is empty
  3. Includes the column labels A1:Z1 from the first sheet only
     =QUERY(
        {March!A1:Z; April!A2:Z; June!A2:Z}, 
        "where Col1 is not null", 1)
    
  • headers is set to 1 so QUERY doesn't see it as part of the data and allows further customization of the query statement such as sorting the data while leaving the heading at the top.
  • You can learn more about the {} syntax for creating arrays here.

Important Notes about Mixed Data

  1. QUERY expects each column's data to contain either text strings or numbers, but not both.
  2. QUERY will typically assign a column's data type based on which data type occurs in it more frequently (more values) or numbers.
  3. Function syntax that may work for one data type may return an error or unexpected results. FILTER is often used for mixed data, or incorporated into the QUERY to avoid issues.

Mixed Data Examples:

Common Source Data

  • used for all examples that follow
  • assume that each column represents a different version of Column A.
  • the operators are used to indicate the relative count in columns with mixed types ie. Text <= # means that for that column there are an equal or smaller number of cells containing text values as those containing numbers.
  All #     Text     Text <= #     Text > #                          
1 1
1 one one
one
two

Example 1 (Formula and Results)

=QUERY(A:A, "where A != ''", 0)
  #     Text     Text <= #     Text > #                          
#N/A one #N/A 1 1 is no longer a # in Text > # results
one
two

Example 2

=QUERY(A:A, "where A is not null", 0)
  #     Text     Text <= #     Text > #                          
1 one 1 1 1 is no longer a # in Text > # results
one
two

Example 3

=QUERY(A:A, "select SUM(A) label SUM(A) ''", 0)
  #     Text     Text <= #     Text > #                          
1 #VALUE! 1 #VALUE!
1
  • I edited your answer to correct some errors and add additional context and explanations. I appreciate that it was a significant edit and if you are not okay with it don't hesitate to roll it back and I will add it as a separate answer. Once you acknowledge this comment I will remove it.
    – Blindspots
    Commented Mar 5 at 21:15
1

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])
1
  • 1
    Thanks for a shorter and a more concise answer. Commented Mar 5 at 7:58
-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)))
1
  • This answer would be improved if you explained why you apply the FLATTEN function to column A given that the function's sole purpose is to flatten multiple ranges into a single column which A:A is already. Otherwise, it seems to be a duplicate of the accepted answer.
    – Blindspots
    Commented Mar 5 at 15:38

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