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

 

[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())),"")

Hi, I got one more thing to ask

This formula is awesome, if i have to find all possible matches for just one word.

Can we change this formula to find all the possible matches for all the words in column A at one go?

To me it seems like excel limitation.

 

[alansidman]

Here is a link to my file that used Power Query for a Fuzzy Match. You can download and review the steps taken.

PQ Fuzzy Merge.xlsx | Powered by Box

app.box.com

 

[ashish002]

Here is a link to my file that used Power Query for a Fuzzy Match. You can download and review the steps taken.

PQ Fuzzy Merge.xlsx | Powered by Box

app.box.com

I checked the file, it looks like my version is different.

It's not allowing me to execute that query.

Anyways, I did got to learn something new.

Thanks for your reply.

 

[alansidman]

Perhaps if you load your sample data to Box.net or some other third party file, we can get you where you need to be with this option. I am not sure what you mean by your version being different. Do you mean Excel or your file is set up differently. Anyway, some sample data would go a long way to helping you.

 

[ashish002]

Perhaps if you load your sample data to Box.net or some other third party file, we can get you where you need to be with this option. I am not sure what you mean by your version being different. Do you mean Excel or your file is set up differently. Anyway, some sample data would go a long way to helping you.

By different version i mean, mine is office 2016 and yours i noticed that it is office 365 if am not wrong.

I am posting the link for sample file.

Fuzzy Match File.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[alansidman]

There should be no difference in how PQ works in 2016 and 365 except for intellisense which you do not need for this project. I am trying to understand what your last request was. Would a count of each name be what you are looking for. Here is an example of what I mean.

Fuzzy Match File.xlsx
	A	B
1	Company Name	Count
2	Ab Bernat	1
3	Abata	2
4	Abatz	3
5	Abby Stribling	1
6	Abbye Moth	1
7	Abner Warrender	1
8	Abran Tuckerman	1
9	Ada Huonic	1
10	Adair Scamel	1
Dupe

 

[ashish002]

There should be no difference in how PQ works in 2016 and 365 except for intellisense which you do not need for this project. I am trying to understand what your last request was. Would a count of each name be what you are looking for. Here is an example of what I mean.

Fuzzy Match File.xlsx
	A	B
1	Company Name	Count
2	Ab Bernat	1
3	Abata	2
4	Abatz	3
5	Abby Stribling	1
6	Abbye Moth	1
7	Abner Warrender	1
8	Abran Tuckerman	1
9	Ada Huonic	1
10	Adair Scamel	1
Dupe

Column A has all the data as you have shown in the snapshot (there are some duplicate values which can be easily found out using conditional formatting).

Apart from that there are many values like for eg: Samsung, Samsung Inc, Samsung Corp, The Samsung). I need to find such values for every cell in column A.

If i type the formula in cell B1 and drag it down, it should give me all the possible partial/fuzzy match values for every cell in column A

Eg: Column A
Samsung
Philips
LG
General
The Samsung
Samsung Inc
The LG
LG Inc
General Electric
General Inc.
The General

For the above mentioned example, for the cell A1 i.e. Samsung i want all the possible words containing Samsung in cell B and likewise for Philips, LG & General

But, the thing here is that i cannot use Fuzzy lookup add in, nor the vba code

As far as i know, i can't even use table.fuzzynestedjoin in power query since that query and fuzzy lookup function is available only to office 365 users

Thanks again for your reply

 

[alansidman]

I forgot that Fuzzy Lookup in PQ was only just added to 365. My apologies. It does look like Toady has you covered.

 

[ashish002]

I forgot that Fuzzy Lookup in PQ was only just added to 365. My apologies. It does look like Toady has you covered.

No worries,

Thanks for all the effort.

 

[sleek12]

@alansidman