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.


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! – Moiz Tankiwala Jan 10 '15 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 '15 at 13:07
  • @JVP, you are right, your formula also works. Thanks! – Moiz Tankiwala Jan 14 '15 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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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