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
 

The Gent

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

It can't be a partial match no, as all rows will give a partial match.

In the below, I wouldn't want the first row excluded as it doesn't match the criteria for exclusion based on its column AO value i.e. CA000993.

I would want the second and third rows to be flagged as exclusions as they match on the columns: D; F; AO, they have a value in AT that when checked against the exclusions list is a wildcard (*) or this could be blank.



Sheet 1 - Report
Column DColumn FColumn AOColumn ATDesired Result
d05PL00693CA000993MA
d05PL00693CA001715EWX
d05PL00693CA001715MAX
d05PL00693CA001190EW

Sheet 2 - Exclusions List
Column AColumn BColumn IColumn L
d05PL00693CA001190MA
d05PL00693CA001511*
d05PL00693CA001715*
d05PL00693CA000984*
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
696
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
In case Power Query is an option, then this is one way

Load Exclusions as connection only as followed:
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="tExclusions"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Make", type text}, {"Model", type text}, {"Fuel", type text}, {"Engine", type text}}),
    MergeCols = Table.CombineColumns(ChangeType,{"Make", "Model", "Fuel", "Engine"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Exclusions"),
    CombinedTextAsList = Table.AddColumn(MergeCols, "ListExclusions", each List.Select(Text.Split([Exclusions], ","), each _ <> "")),
    KeepOnlyList = Table.RemoveColumns(CombinedTextAsList,{"Exclusions"}),
    ListFromLists = KeepOnlyList[ListExclusions]
in
    ListFromLists

Make a custom function "IsExcluded" (blank query and paste code):
Power Query:
(Text as text, list as list) as any =>
let
      T1 = Text.Split(Text, ",")
    , T2= not List.IsEmpty(List.Select(List.Transform(list, each List.ContainsAll(T1, _, Comparer.OrdinalIgnoreCase) ), each _ = true))
    , T3 = if T2 then "X" else null
  
in
    T3

Apply the custom function on the data table:
Power Query:
let
    bExclusions= List.Buffer(Exclusions),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Make", type text}, {"Model", type text}, {"Fuel", type text}, {"Engine", type text}}),
    AddCombineText = Table.AddColumn(ChangeType, "CombineText", each Text.Combine({[Make], [Model], [Fuel], [Engine]}, ","), type text),
    InvokeFxIsExcluded = Table.AddColumn(AddCombineText, "Excluded", each IsExcluded([CombineText], bExclusions)),
    RemoveCombinedText = Table.RemoveColumns(InvokeFxIsExcluded,{"CombineText"})
in
    RemoveCombinedText

Load this query as a table on a new sheet (green table).

1609337453298.png
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,826
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Perhaps something like this:

Book1
DEFGHI
2d05PL00693CA000993MAd05|PL00693|CA000993|MA 
3d05PL00693CA001715EWd05|PL00693|CA001715|EWX
4d05PL00693CA001715MAd05|PL00693|CA001715|MAX
5d05PL00693CA001190EWd05|PL00693|CA001190|EW 
Report
Cell Formulas
RangeFormula
H2:H5H2=D2&"|"&E2&"|"&F2&"|"&G2
I2:I5I2=REPT("X",COUNT(1/COUNTIF(H2,'Exclusion list'!E2:E5)))
Press CTRL+SHIFT+ENTER to enter array formulas.


and the exclusion sheet:

Book1
ABCDE
2d05PL00693CA001190MAd05|PL00693|CA001190|MA
3d05PL00693CA001511*d05|PL00693|CA001511|*
4d05PL00693CA001715*d05|PL00693|CA001715|*
5d05PL00693CA000984*d05|PL00693|CA000984|*
Exclusion list
Cell Formulas
RangeFormula
E2:E5E2=A2&"|"&B2&"|"&C2&"|"&D2
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
Perhaps something like this:

Book1
DEFGHI
2d05PL00693CA000993MAd05|PL00693|CA000993|MA 
3d05PL00693CA001715EWd05|PL00693|CA001715|EWX
4d05PL00693CA001715MAd05|PL00693|CA001715|MAX
5d05PL00693CA001190EWd05|PL00693|CA001190|EW 
Report
Cell Formulas
RangeFormula
H2:H5H2=D2&"|"&E2&"|"&F2&"|"&G2
I2:I5I2=REPT("X",COUNT(1/COUNTIF(H2,'Exclusion list'!E2:E5)))
Press CTRL+SHIFT+ENTER to enter array formulas.


and the exclusion sheet:

Book1
ABCDE
2d05PL00693CA001190MAd05|PL00693|CA001190|MA
3d05PL00693CA001511*d05|PL00693|CA001511|*
4d05PL00693CA001715*d05|PL00693|CA001715|*
5d05PL00693CA000984*d05|PL00693|CA000984|*
Exclusion list
Cell Formulas
RangeFormula
E2:E5E2=A2&"|"&B2&"|"&C2&"|"&D2
Hi,

Thanks for the suggestion but it doesn't look like this will work due to the volume of data, ~45k rows.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,826
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

There was no noticeable lag for me - is it very slow for you? How many rows of exclusions do you have?
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
There was no noticeable lag for me - is it very slow for you? How many rows of exclusions do you have?
Yes, it actually won't process. 45k rows in the report, 100 lines of exclusions in the second sheet.
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46

ADVERTISEMENT

In case Power Query is an option, then this is one way

Load Exclusions as connection only as followed:
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="tExclusions"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Make", type text}, {"Model", type text}, {"Fuel", type text}, {"Engine", type text}}),
    MergeCols = Table.CombineColumns(ChangeType,{"Make", "Model", "Fuel", "Engine"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Exclusions"),
    CombinedTextAsList = Table.AddColumn(MergeCols, "ListExclusions", each List.Select(Text.Split([Exclusions], ","), each _ <> "")),
    KeepOnlyList = Table.RemoveColumns(CombinedTextAsList,{"Exclusions"}),
    ListFromLists = KeepOnlyList[ListExclusions]
in
    ListFromLists

Make a custom function "IsExcluded" (blank query and paste code):
Power Query:
(Text as text, list as list) as any =>
let
      T1 = Text.Split(Text, ",")
    , T2= not List.IsEmpty(List.Select(List.Transform(list, each List.ContainsAll(T1, _, Comparer.OrdinalIgnoreCase) ), each _ = true))
    , T3 = if T2 then "X" else null
 
in
    T3

Apply the custom function on the data table:
Power Query:
let
    bExclusions= List.Buffer(Exclusions),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Make", type text}, {"Model", type text}, {"Fuel", type text}, {"Engine", type text}}),
    AddCombineText = Table.AddColumn(ChangeType, "CombineText", each Text.Combine({[Make], [Model], [Fuel], [Engine]}, ","), type text),
    InvokeFxIsExcluded = Table.AddColumn(AddCombineText, "Excluded", each IsExcluded([CombineText], bExclusions)),
    RemoveCombinedText = Table.RemoveColumns(InvokeFxIsExcluded,{"CombineText"})
in
    RemoveCombinedText

Load this query as a table on a new sheet (green table).

View attachment 28785
This looks simple and effective but I have no experience with Power Query, how do I setup the initial step?
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
There was no noticeable lag for me - is it very slow for you? How many rows of exclusions do you have?
Actually - apologies, I selected a column as opposed to range on the Exclusions side...

Retrying now.
 

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Looking again at your data structure and depending on whether your data and criteria are fairly fixed or changeable, I reckon advanced filter is viable.

As always, I’d recommend you back up your data. But this seems to work.

Ensure your data table and criteria table have the same headings. As you may know, advanced filter relies on their being unique matching column headers.

Use your data range and criteria range as is with the above caveats. Plus leave a blank in any column for wildcard.

Select to filter the list in place (data > advanced filter )

Then with the filter applied add an X to all the filtered rows
You can then remove the filter to view all rows with blank showing if included and X for excluded.
Any good?

You could potentially automate this but if your data is fairly static it should not take long to run through this process.
 

The Gent

New Member
Joined
Jul 23, 2019
Messages
46
Perhaps something like this:

Book1
DEFGHI
2d05PL00693CA000993MAd05|PL00693|CA000993|MA 
3d05PL00693CA001715EWd05|PL00693|CA001715|EWX
4d05PL00693CA001715MAd05|PL00693|CA001715|MAX
5d05PL00693CA001190EWd05|PL00693|CA001190|EW 
Report
Cell Formulas
RangeFormula
H2:H5H2=D2&"|"&E2&"|"&F2&"|"&G2
I2:I5I2=REPT("X",COUNT(1/COUNTIF(H2,'Exclusion list'!E2:E5)))
Press CTRL+SHIFT+ENTER to enter array formulas.


and the exclusion sheet:

Book1
ABCDE
2d05PL00693CA001190MAd05|PL00693|CA001190|MA
3d05PL00693CA001511*d05|PL00693|CA001511|*
4d05PL00693CA001715*d05|PL00693|CA001715|*
5d05PL00693CA000984*d05|PL00693|CA000984|*
Exclusion list
Cell Formulas
RangeFormula
E2:E5E2=A2&"|"&B2&"|"&C2&"|"&D2
This has worked great :)

It seemed so simple in my mind but it was a journey to get to this so thank you for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,023
Messages
5,575,631
Members
412,680
Latest member
TSpan
Top