Match to a list with wildcards

T

The Gent

Board Regular
Joined
Jul 23, 2019
Messages
50
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
 
Sort by date Sort by votes
Xl365 said:
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.
Click to expand...
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*
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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
 
Upvote 0
RoryA said:
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
Click to expand...
Hi,

Thanks for the suggestion but it doesn't look like this will work due to the volume of data, ~45k rows.
 
Upvote 0
There was no noticeable lag for me - is it very slow for you? How many rows of exclusions do you have?
 
Upvote 0
GraH said:
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
Click to expand...
This looks simple and effective but I have no experience with Power Query, how do I setup the initial step?
 
Upvote 0
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.
 
Upvote 0
RoryA said:
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
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
SUMIFS exclude value if partial match in list/named range
2
Replies
18
Views
385
Alex Blakenburg
A
B
  • Question
Need help creating a second ListBoxOutput without corrupting the first.
Replies
3
Views
290
Blasirl
B
T
  • Question
Matching List of Name is different Sheets - Copying Adjacent cell values from one to the other
Replies
3
Views
317
DanteAmor
DanteAmor
C
  • Question
Complex VBA code - Multiple IF conditions including Worksheet Name contains text partially matching Cell
Replies
1
Views
3K
celebwen_orn
C
mzza
  • Question
"Looking up" based on concatenation of values, some of which fall within a range of x to y
Replies
8
Views
906
Swayzy
S

Forum statistics

Threads
1,214,591
Messages
6,120,424
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top