Data filters - how to use multiple filters to select rows, without adding further columns

[stefanaalten]

Hi,

I often find I want to use data filters to select rows based on multiple columns, but can't seem to do this without adding extra columns to artificially create the OR condition. Here's a simplified example to illustrate my question. In practice I have a spreadsheet with several thousand rows and around 30 columns each with indicators (mainly of the "Yes/No" variety)

Book1
	A	B	C	D
1	Item	Colour	Matter	Colour or matter
2	A	Blue	Solid	Blue or solid
3	B	Blue	Liquid	Blue or solid
4	C	Red	Solid	Blue or solid
5	D	Red	Liquid	Neither blue nor solid
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(OR(B2="Blue",C2="Solid"),"Blue or solid","Neither blue nor solid")

In the example I can easily use the filters (i.e. the drop-down lists with check boxes) to create an AND style query without needing the extra column, i.e. show me rows where the item is both blue and solid. However, if I want to see rows where the item is either blue or solid (or both), I need to add a new column.

In the trivial example that's easily done, but with 30 columns the permutations multiply massively. Is there a way of using existing filters to create an OR type query?

Many thanks!

 

[Cubist]

If you want to use the built-in filter functionality, you'd need to use VBA. One way to query using formula:

Book2
	A	B	C	D	E	F	G	H	I
1	Item	Colour	Matter		Criteria		Item	Colour	Matter
2	A	Blue	Solid		Red		A	Blue	Solid
3	B	Blue	Liquid		Blue		B	Blue	Liquid
4	C	Red	Solid		Solid		C	Red	Solid
5	D	Pink	Solid				D	Pink	Solid
Sheet4

Cell Formulas
Range	Formula
G2:I5	G2	=FILTER(A2:C5,BYROW(A2:C5,LAMBDA(x,SUM(COUNTIFS(x,E2:E4)))))
Dynamic array formulas.

 

[stefanaalten]

If you want to use the built-in filter functionality, you'd need to use VBA. One way to query using formula:

Book2
	A	B	C	D	E	F	G	H	I
1	Item	Colour	Matter		Criteria		Item	Colour	Matter
2	A	Blue	Solid		Red		A	Blue	Solid
3	B	Blue	Liquid		Blue		B	Blue	Liquid
4	C	Red	Solid		Solid		C	Red	Solid
5	D	Pink	Solid				D	Pink	Solid
Sheet4

Cell Formulas
Range	Formula
G2:I5	G2	=FILTER(A2:C5,BYROW(A2:C5,LAMBDA(x,SUM(COUNTIFS(x,E2:E4)))))
Dynamic array formulas.

If you want to use the built-in filter functionality, you'd need to use VBA. One way to query using formula:

Book2
	A	B	C	D	E	F	G	H	I
1	Item	Colour	Matter		Criteria		Item	Colour	Matter
2	A	Blue	Solid		Red		A	Blue	Solid
3	B	Blue	Liquid		Blue		B	Blue	Liquid
4	C	Red	Solid		Solid		C	Red	Solid
5	D	Pink	Solid				D	Pink	Solid
Sheet4

Cell Formulas
Range	Formula
G2:I5	G2	=FILTER(A2:C5,BYROW(A2:C5,LAMBDA(x,SUM(COUNTIFS(x,E2:E4)))))
Dynamic array formulas.

Thanks Cubist, but your solution - if I understand correctly - still means adding a separate query for each possible combination. With my 30 columns and combination of multiple columns (i.e. more than just two as per my noddy example) that would become impractical. I was thinking of a way of selecting (ticking in a list) values in multiple columns and finding rows where any one of the selected values is met. Is there an Excel plugin that could do this perhaps?

 

[Cubist]

My solution checks if at least one of the columns (A,B, or C) fits at least one of the criteria in E - return that row.