Match to a list with wildcards

The Gent

New Member
Joined
Jul 23, 2019
Messages
44
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:

ReportExclusion
MakeModelFuelEngineExclude?MakeModelFuelEngine
FordFiestaPetrolFour***Six
FordMondeoDieselFour
ToyotaYarisPetrolTwo
JaguarFPetrolSixX
VolkswagonPoloDieselFour
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

The Gent

New Member
Joined
Jul 23, 2019
Messages
44
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,807
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I think it would be much simpler to use 4 MATCH functions and just leave the non-excluded columns blank.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
694
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hello,

I hope my reading of your problem is correct. This seems to work?
Test Date grouping in pivot without saving cache.xlsx
ABCDEFGHIJKLMNOP
1ReportExclusion
2MakeModelFuelEngineExclude?MakeModelFuelEngineExcludeString
3FordFiestaPetrolFourFALSESixSix
4FordMondeoDieselFourFALSEPoloDieselPolo Diesel
5ToyotaYarisPetrolTwoFALSEFordFiestaDieselFourFord Fiesta Diesel Four
6JaguarFPetrolSixTRUE 
7VolkswagonPoloDieselFourTRUE
8FordFiestaDieselFourTRUE
9
10
Sheet3
Cell Formulas
RangeFormula
N3:N5,O6N3=TEXTJOIN(" ",TRUE,J3:M3)
F3:F8F3=ISNUMBER(AGGREGATE(15,6,ROW($N$3:$N$5)/SEARCH($N$3:$N$5,TEXTJOIN(" ",TRUE,B3:E3)),1))
 

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
245
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

New Member
Joined
Jul 23, 2019
Messages
44

ADVERTISEMENT

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

Board Regular
Joined
Apr 29, 2016
Messages
245
Or try:

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

The Gent

New Member
Joined
Jul 23, 2019
Messages
44
Hello,

I hope my reading of your problem is correct. This seems to work?
Test Date grouping in pivot without saving cache.xlsx
ABCDEFGHIJKLMNOP
1ReportExclusion
2MakeModelFuelEngineExclude?MakeModelFuelEngineExcludeString
3FordFiestaPetrolFourFALSESixSix
4FordMondeoDieselFourFALSEPoloDieselPolo Diesel
5ToyotaYarisPetrolTwoFALSEFordFiestaDieselFourFord Fiesta Diesel Four
6JaguarFPetrolSixTRUE 
7VolkswagonPoloDieselFourTRUE
8FordFiestaDieselFourTRUE
9
10
Sheet3
Cell Formulas
RangeFormula
N3:N5,O6N3=TEXTJOIN(" ",TRUE,J3:M3)
F3:F8F3=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

New Member
Joined
Jul 23, 2019
Messages
44
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,174
Messages
5,570,684
Members
412,336
Latest member
Tiffany927
Top