Match to a list with wildcards

[The Gent]

Good day all,

I am trying to do something which it my head feels quite simple but I can't get a result.

I have four dimensions in a spreadsheet report, lets say: make; model; fuel; engine.

Separately i have a list of exclusions which is a series of the four dimensions that I want to strip out of the report when analysing the report.

My exclusions list is also four dimensions but I want to be able to use wildcards to exclude certain combinations of the report.

The plan is to have a match function in the original report and use a flag to highlight those rows which match the criteria on the exclusions list. In the example below, on the exclusions list I want to exclude any row which has six as the engine, therefore I have used wildcards on the exclusions list for all other dimensions. This doesn't seem to work though.

Any ideas?


For example:

Report						Exclusion
Make	Model	Fuel	Engine	Exclude?		Make	Model	Fuel	Engine
Ford	Fiesta	Petrol	Four			*	*	*	Six
Ford	Mondeo	Diesel	Four
Toyota	Yaris	Petrol	Two
Jaguar	F	Petrol	Six	X
Volkswagon	Polo	Diesel	Four

 

[The Gent]

Note - this is the formula I tried to use on the report - =IFNA(IF(MATCH(D2249&"."&F2249&"."&AO2249&"."&AT2249,EXCLUSIONS!$N:$N,0),"X"),"")

EXCLUSIONS!$N:$N being the concatenation of the four fields.

 

[The Gent]

Help...

 

[RoryA]

I think it would be much simpler to use 4 MATCH functions and just leave the non-excluded columns blank.

 

[GraH]

Hello,

I hope my reading of your problem is correct. This seems to work?

Test Date grouping in pivot without saving cache.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Report								Exclusion
2		Make	Model	Fuel	Engine	Exclude?				Make	Model	Fuel	Engine	ExcludeString
3		Ford	Fiesta	Petrol	Four	FALSE							Six	Six
4		Ford	Mondeo	Diesel	Four	FALSE					Polo	Diesel		Polo Diesel
5		Toyota	Yaris	Petrol	Two	FALSE				Ford	Fiesta	Diesel	Four	Ford Fiesta Diesel Four
6		Jaguar	F	Petrol	Six	TRUE
7		Volkswagon	Polo	Diesel	Four	TRUE
8		Ford	Fiesta	Diesel	Four	TRUE
9
10
Sheet3

Cell Formulas
Range	Formula
N3:N5,O6	N3	=TEXTJOIN(" ",TRUE,J3:M3)
F3:F8	F3	=ISNUMBER(AGGREGATE(15,6,ROW($N$3:$N$5)/SEARCH($N$3:$N$5,TEXTJOIN(" ",TRUE,B3:E3)),1))

 

[Phuoc]

Try this:

=IF(SUM(IF($J$3:$M$3="",1,--(B3:E3=$J$3:$M$3)))=4,"X","")

Enter with Ctrl+Shift+Enter.

 

[The Gent]

I think it would be much simpler to use 4 MATCH functions and just leave the non-excluded columns blank.

Can you show me an example?

Thanks

 

[Phuoc]

Or try:

=IF(SUMPRODUCT((B3:E3=$J$3:$M$3)+($J$3:$M$3=""))=4,"X","")

 

[The Gent]

Hello,

I hope my reading of your problem is correct. This seems to work?

Test Date grouping in pivot without saving cache.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Report								Exclusion
2		Make	Model	Fuel	Engine	Exclude?				Make	Model	Fuel	Engine	ExcludeString
3		Ford	Fiesta	Petrol	Four	FALSE							Six	Six
4		Ford	Mondeo	Diesel	Four	FALSE					Polo	Diesel		Polo Diesel
5		Toyota	Yaris	Petrol	Two	FALSE				Ford	Fiesta	Diesel	Four	Ford Fiesta Diesel Four
6		Jaguar	F	Petrol	Six	TRUE
7		Volkswagon	Polo	Diesel	Four	TRUE
8		Ford	Fiesta	Diesel	Four	TRUE
9
10
Sheet3

Cell Formulas
Range	Formula
N3:N5,O6	N3	=TEXTJOIN(" ",TRUE,J3:M3)
F3:F8	F3	=ISNUMBER(AGGREGATE(15,6,ROW($N$3:$N$5)/SEARCH($N$3:$N$5,TEXTJOIN(" ",TRUE,B3:E3)),1))

Hi,

Thanks for the suggestion. I think this is going to burnout my file though, I should have mentioned I have ~40k rows of data in each report and so it doesn't seem to be able to cope with this.

 

[The Gent]

Try this:

=IF(SUM(IF($J$3:$M$3="",1,--(B3:E3=$J$3:$M$3)))=4,"X","")

Enter with Ctrl+Shift+Enter.

This only seems to look at one row but then doesn't return the desired result when I expand to multiple rows i.e. $J$3:$M$5.