Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

[ashish002]

Hi,

Is there a formula to find out all the possible fuzzy/partial matches for a single value (there is only one column in the sheet)?

Column A has all the data (there are some duplicate values which can be found out using conditional formatting). Apart from that there are many values like Samsung, Samsung Inc, Samsung Corp, The Samsung)

If i type the formula in cell B1, it should give me all the possible partial match values for cell A1 and likewise

Note: Fuzzy Lookup add in, power query and vba cannot be used

Thanks in advance

 

[Toadstool]

Hi Ashish002,

SEARCH will do that for you.

Ashish202.xlsx
	A	B	C	D
1	Samsung	Samsung		Samsung
2	Samsung Inc			Samsung Inc
3	Samsung Corp			Samsung Corp
4	The Samsung			The Samsung
5	Apple			My Samsung LLC
6	Apple Corp.
7	Apple Corp
8	VONS
9	The VONS
10	The Apple
11	My Samsung LLC
12
Sheet1

Cell Formulas
Range	Formula
D1:D11	D1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$9999)/(ISNUMBER(SEARCH($B$1,$A$1:$A$9999))),ROW())),"")

 

[alansidman]

An alternative is to use Power Query and join each column as a table in the Editor with a left outer join fuzzy match.

let
    Source = Table.FuzzyNestedJoin(Table2, {"Column1"}, Table1, {"Column1"}, "Table1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=.5]),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Column1"}, {"Table1.Column1"})
in
    #"Expanded Table1"

 

[ashish002]

An alternative is to use Power Query and join each column as a table in the Editor with a left outer join fuzzy match.

let
    Source = Table.FuzzyNestedJoin(Table2, {"Column1"}, Table1, {"Column1"}, "Table1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=.5]),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Column1"}, {"Table1.Column1"})
in
    #"Expanded Table1"

Didn't work out in Excel 2016.

Thanks for your time.

 

[ashish002]

Hi Ashish002,

SEARCH will do that for you.

Ashish202.xlsx
	A	B	C	D
1	Samsung	Samsung		Samsung
2	Samsung Inc			Samsung Inc
3	Samsung Corp			Samsung Corp
4	The Samsung			The Samsung
5	Apple			My Samsung LLC
6	Apple Corp.
7	Apple Corp
8	VONS
9	The VONS
10	The Apple
11	My Samsung LLC
12
Sheet1

Cell Formulas
Range	Formula
D1:D11	D1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$9999)/(ISNUMBER(SEARCH($B$1,$A$1:$A$9999))),ROW())),"")

Thanks a lot, It worked

Can we optimise this formula for a larger data set where the no. of rows are greater than 20000?

 

[Toadstool]

Thanks a lot, It worked

Can we optimise this formula for a larger data set where the no. of rows are greater than 20000?

You're welcome!

I dummied up 25,000 rows of column A and copied down the column D formula 200 rows and it took about 2.5 seconds on my small laptop.

It's mostly a function of how many rows you copy down the AGGREGATE formula. (i.e. how many hits you may find). You could remove the IFERROR, if you're OK to get #NUM! errors after the last hit, which would give a marginal performance increase but apart from that I don't see how it could be made faster.

Ashish202.xlsx
	A	B	C	D
1	Samsung	Apple		Apple
2	Samsung Inc			Apple Corp.
3	Samsung Corp			Apple Corp
4	The Samsung			The Apple
5	Apple			Apple
6	Apple Corp.			Apple Corp.
7	Apple Corp			Apple Corp
8	VONS			The Apple
9	The VONS			Apple
10	The Apple			Apple Corp.
Sheet1 (2)

Cell Formulas
Range	Formula
D1:D10	D1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$25000)/(ISNUMBER(SEARCH($B$1,$A$1:$A$25000))),ROW())),"")

 

[ashish002]

You're welcome!

I dummied up 25,000 rows of column A and copied down the column D formula 200 rows and it took about 2.5 seconds on my small laptop.

It's mostly a function of how many rows you copy down the AGGREGATE formula. (i.e. how many hits you may find). You could remove the IFERROR, if you're OK to get #NUM! errors after the last hit, which would give a marginal performance increase but apart from that I don't see how it could be made faster.

Ashish202.xlsx
	A	B	C	D
1	Samsung	Apple		Apple
2	Samsung Inc			Apple Corp.
3	Samsung Corp			Apple Corp
4	The Samsung			The Apple
5	Apple			Apple
6	Apple Corp.			Apple Corp.
7	Apple Corp			Apple Corp
8	VONS			The Apple
9	The VONS			Apple
10	The Apple			Apple Corp.
Sheet1 (2)

Cell Formulas
Range	Formula
D1:D10	D1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$25000)/(ISNUMBER(SEARCH($B$1,$A$1:$A$25000))),ROW())),"")

Thanks again for all your help.

 

[Toadstool]

Thanks again for all your help.

You're welcome.

 

[alansidman]

What does this mean? Need more explanation to help. It worked for me.

Didn't work out in Excel 2016.

​

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[ashish002]

What does this mean? Need more explanation to help. It worked for me.

​

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

I tried again using the code. I pasted the code as is in the blank query editor and when i clicked on done, it showed me expression error saying table.fuzzynestedjoin wasn't recognized.

Please let me know if am doing something wrong.