Filtering a Sheet across multiple columns

Question

Looking for some help with a filtering problem. I have a sheet with dozens of columns, any one of which may contain data a user might want to filter on. For usability sake, what I want to do is create a filter tab that has some empty cells a user can populate and be returned a list of all the rows that contained all the search terms.

Example:

The data in this example has rows 1, 2 and 3. Based on the selection criteria (Denver, Product 1 and Skill 1), only the 2nd row contains all three search terms.

Is there a filter function what will search across rows and columns?

https://docs.google.com/spreadsheets/d/1nMu00tyCC0gEw4IPuaXG1nb50Xj7IYQ9Gcmq6C7xgfs/edit?usp=sharing

player0 · Accepted Answer · 2020-05-04 22:34:48Z

3

try:

=ARRAYFORMULA(ARRAY_CONSTRAIN(QUERY({A7:J9, 
 TRANSPOSE(QUERY(TRANSPOSE(A7:J9),,99^99))}, 
 "where "&JOIN(" and ", IF(B1:B3="",,
 "lower(Col"&COLUMN(J9)+1&") contains '"&
 LOWER(B1:B3)&"'")), 0), 99^99, COLUMNS(A7:J9)))

answered May 4, 2020 at 22:34

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Rosendin Over a year ago

Hi @player0 - that works beautifully. Thank you so much. Can you please add a little explanation of what's going on?

player0 Over a year ago

sure, well idea is to collapse all columns into single one (Col11) with "transpose(query(transpose" combo and search B1:B3 values in it. next we use query where we can use generated string of commands (the Join function) based on the presence of B1:B3 values (the IF function), query is case sensitive so we use LOWER to counter it and then finally we use Array_constrain to return only needed columns eg not to output the 11th column

kirkg13 Over a year ago

@player0 Thanks for the great explanation - really helps me to learn more by better understanding what the formula is doing.

Collectives™ on Stack Overflow

Filtering a Sheet across multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related