I do not know how to explain this perfectly without a picture so please take a look at the image before reading.

FIG 1.

I am trying to filter the data in Fig 1 (Above). based on the headers and then if the numbered column within that header is not 0 or blank. However, I also need the data from column A to be included in this filter.

For example, Take a look at the header "Bike" in column H. I want to be able to filter the data from only column H and column I because its within the "Boat" header. Then, I also need the blank cells and cells with 0 to not be included in the filter.

In the end it should look like Fig. 2 (Below) after it is filtered. I have tried doing filter if, just filter, etc. and still have not been able to figure it out. If I have to change my format as well let me know.

FIG 2.

Thank you so much in advance!

1 Answer 1


Edit per OP's comment
(This works great if the rows have no spaces in them but my data does have extra blank rows in between the rows of data as well.)

=IFNA(QUERY({A3:I11},"select Col1, Col"&MATCH(B16,A2:I2,0)&", Col"&MATCH(B16,A2:I2,0)+1&" 
                     where Col"&MATCH(B16,A2:I2,0)&" !=0 and Col1 is not null"),"missing")

No problem. Using the above formula you can target and filter each and every column in the query. Keep any or all of them according to your needs.

You would need the use of the QUERY function.

=QUERY({A4:A8,D4:E8},"select Col1, Col2, Col3 where Col2 !=0")

Query non-consecutive columns

Using a "helper" cell e.g. B11 try the following for a more dynamic result:

=IFNA(QUERY({A3:I7},"select Col1, Col"&MATCH(B10,A2:I2,0)&", Col3 
                     where Col"&MATCH(B10,A2:I2,0)&" !=0"),"missing")

To completely automate the process, using Data validation, create a drop-down on cell B11, set criteria List from a range and pick the headers (B2:I2).

Query non-consecutive columns based on headers drop-down list

Functions used:

  • 1
    Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Commented Feb 26, 2020 at 4:58
  • This works great if the rows have no spaces in them but my data does have extra blank rows in between the rows of data as well. I know it may seem as though those blank rows are not needed in the initial fig. 1, but they are. However, when I do the formula you gave me it also brings over the blank rows, which I do not need as shown in fig. 2. Do you know of any way to fix this?Thank you so much again!
    – Lina
    Commented Feb 26, 2020 at 13:40
  • Amazing!!! Thank you so much for your help!
    – Lina
    Commented Feb 26, 2020 at 20:55
  • One more question. So what if there are blanks and 0's in the number columns instead of just 0's? @marikamitsos
    – Lina
    Commented Feb 27, 2020 at 18:30
  • What have you tried? Check and try to read how the query is structured and what we did with the other columns. If still in trouble let me know. Commented Feb 28, 2020 at 11:05

Your Answer

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