Match to a list with wildcards

The Gent

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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
257
Try again:

=IF(SUMPRODUCT(--(MMULT((B3:E3=$J$3:$M$5)+($J$3:$M$5=""),{1;1;1;1})=4)),"X","")
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
Try again:

=IF(SUMPRODUCT(--(MMULT((B3:E3=$J$3:$M$5)+($J$3:$M$5=""),{1;1;1;1})=4)),"X","")
This is working on my simple example but when I apply it to my larger dataset it fails...

Excel Formula:
=IF(SUMPRODUCT(--(MMULT((D2252,F2252,AO2252,AT2252=EXCLUSIONS!A:A,EXCLUSIONS!B:B,EXCLUSIONS!I:I,EXCLUSIONS!L:L)+(EXCLUSIONS!A:A,EXCLUSIONS!B:B,EXCLUSIONS!I:I,EXCLUSIONS!L:L=""),{1;1;1;1})=4)),"X","")

Any ideas?
 

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Picking up on an earlier suggestion, you could use four MATCH functions to check for match including wildcards.

If I understand the requirement correctly, this would allow you to exclude for example Jaguar or Jag.
For Jag just type Jag*, for example.

Adjust the range for your set up.

Excel Formula:
=IF(ISNUMBER(MATCH($F$3,A3,0))+ISNUMBER(MATCH($G$3,B3,0))+ISNUMBER(MATCH($H$3,C3,0))+ISNUMBER(MATCH($I$3,D3,0))>0,"X","")
 

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

To add that if you want to have the wildcards automatically included, I would suggest you use a helper row to surround the match text with asterisks.
for example
Excel Formula:
=“*” & $H$3 &  “*”
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
Picking up on an earlier suggestion, you could use four MATCH functions to check for match including wildcards.

If I understand the requirement correctly, this would allow you to exclude for example Jaguar or Jag.
For Jag just type Jag*, for example.

Adjust the range for your set up.

Excel Formula:
=IF(ISNUMBER(MATCH($F$3,A3,0))+ISNUMBER(MATCH($G$3,B3,0))+ISNUMBER(MATCH($H$3,C3,0))+ISNUMBER(MATCH($I$3,D3,0))>0,"X","")
Hi,

Unfortunately this excludes items even with a partial match...

I will try to use an example from my real data set now.

When I use the above formula it returns the X flag for all items as at least one field is always in the exclusions list.

Below I have shown what the desired X flags would be.

Sheet 1 - Report​
Column D​
Column F​
Column AO​
Column AT​
Desired Result​
d05​
PL00693​
CA000993​
MA​
d05​
PL00693​
CA001715​
EW​
X​
d05​
PL00693​
CA001715​
MA​
X​
d05​
PL00693​
CA001190​
EW​

Sheet 2 - Exclusions List​
Column A​
Column B​
Column I​
Column L​
d05​
PL00693​
CA001190​
MA​
d05​
PL00693​
CA001511​
*​
d05​
PL00693​
CA001715​
*​
d05​
PL00693​
CA000984​
*​
 

GraH

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

ADVERTISEMENT

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.
I tried the formula on 100K rows and it seems to work. How many exclusions are we talking about? I went up to nearly 20K and all my rows were calculated in under 3 seconds.

EDIT: Wait, it did not, my Excel was in freeze modus, I did not even notice. :eek:

Maybe an alternative with Power Query or VBA would be more appropriate?
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
I tried the formula on 100K rows and it seems to work. How many exclusions are we talking about? I went up to nearly 20K and all my rows were calculated in under 3 seconds.

EDIT: Wait, it did not, my Excel was in freeze modus, I did not even notice. :eek:

Maybe an alternative with Power Query or VBA would be more appropriate?
My report is 45k rows and the exclusions are limited to <100.
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
This seems to work well...

Excel Formula:
=IF(SUMPRODUCT(--(MMULT((D6:G6=$M$5:$P$8)+($M$5:$P$8=""),{1;1;1;1})=4)),"X","")

However, this doesn't...

Excel Formula:
=IF(SUMPRODUCT(--(MMULT((D6,E6,F6,G6=$M$5:$P$8)+($M$5:$P$8=""),{1;1;1;1})=4)),"X","")

How can I get this to work when the columns in the report are not alongside each other?
 

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
I am not clear on your exclusion criteria. I understood that you wished to include partial matches.
Why is the MA line not matched?
Are you looking for a match on all columns with the option to have any characters in one or more columns?
If so, change the > 0 to > 3 in the formula I posted.
And link to a 2nd row with the * asterisk wildcard formulas as I mentioned above.
then insert the search terms in the original row. The row below will have eg *Jag* which will be what the match formula looks for.

Typing this on iPhone without Excel.

Just input another character such as # to instead of leaving criteria cells blank, assuming you don’t use the # character in any of the data fields.

Edit: how many exclusions are you looking at?
An alternative method might be to use advanced filter to identify the exclusion rows.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,119,020
Messages
5,575,602
Members
412,679
Latest member
TSpan
Top