Identify fuzzy matches in one column

[Vbanoob98]

Hello!

I have a list of 20k names and I am trying to find the fuzzy duplicates on the same column

Like:

Company Waterfall
Company Waterfll
Company Waterfalls

Ideally those 3 would be highlighted, marked with a 1 or something that allows for identification

Any ideas? been looking for weeks and I can find a lot of similar problems but no solutions :S

 

[Alex Blakenburg]

Have you considered using the Power Query Fuzzy Matching functionality ?
If you don't have Power Query or PQ's fuzzy matching how about the Excel Fuzzy matching Add-In that you can get from the Microsoft site here:
Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center

 

[Vbanoob98]

Have you considered using the Power Query Fuzzy Matching functionality ?
If you don't have Power Query or PQ's fuzzy matching how about the Excel Fuzzy matching Add-In that you can get from the Microsoft site here:
Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center

Yes, but I want to compare one column only. Thats the tricky part. I dont see a way to use that for this particular issue

 

[Alex Blakenburg]

Here is an option using PQ:

Fuzzy Match of table joined with itself, then concatenating the result.
Probably needs a sort before the concatenate so that all the results are in the same order.

Book5
	A	B
1	Name	New
2	Waterfall	Waterfall|Waterfll|Waterfalls
3	Waterfll	Waterfll|Waterfall|Waterfalls
4	Waterfalls	Waterfalls|Waterfall|Waterfll
5	John Smith	John Smith|John Smyth
6	John Smyth	John Smyth|John Smith
7	John Brown	John Brown
Merge1

 

[Vbanoob98]

Here is an option using PQ:

Fuzzy Match of table joined with itself, then concatenating the result.
Probably needs a sort before the concatenate so that all the results are in the same order.

Book5
	A	B
1	Name	New
2	Waterfall	Waterfall|Waterfll|Waterfalls
3	Waterfll	Waterfll|Waterfall|Waterfalls
4	Waterfalls	Waterfalls|Waterfall|Waterfll
5	John Smith	John Smith|John Smyth
6	John Smyth	John Smyth|John Smith
7	John Brown	John Brown
Merge1

Thats very close to what I want. Ive tried that approach but I couldn't get that result. I'll try again! Thanks!

 

[Alex Blakenburg]

It sounds like you got as far as doing the FuzzyNestedJoin.
The additional step that I have now also modified to show a consistent sort is below.

The fuzzy join to the same Table gives you a column with a Table as contents on each row.
This needs to be converted to a List after which you can Remove the Column with the Table values.
Because we are looking for duplicates we want the concatenated output to be in the same order on each line, so we add a List.Sort into the List conversion.

After that click on the Expand button next to the column heading and select Extract Values (and pick a delimiter)

See if the below is enough to configure it for your specific case.

= Table.AddColumn(RenamedColumnOptional, "New", each List.Sort(
    Table.ToList([Table2])))

 

[Vbanoob98]

Got it working. Thank you very much for all your help!

 

[Alex Blakenburg]

Removed, response to a post before it was edited.

 

[Alex Blakenburg]

That’s great. Thanks for letting me know. Glad I could help.

 

[Vbanoob98]

That’s great. Thanks for letting me know. Glad I could help.

I noticed that the concatenation sometimes wraps the names in quotes "", do you know why?