Find closest matches to string within the same column using vba

[andnand]

Hi everyone, hope you are safe and healthy!!

I would like to hear ideas and or code to find and write matches. I have a column with hundreds to thousands of rows of data and would like to find the closest matches within the same column to normalize the list.

finding matches.xlsx
	L
2	Original
3	John Doe
4	John F Doe
5	John Frank Doe Robbins
6	John Frand Doe
7	John D Doe R
8	John F. Doe
9	John Doe
10	Doe, John
Start

Desired output:

finding matches.xlsx
	L	M	N	O
2	Original	Proposed 1	Proposed 2	Proposed 3
3	John Doe	John Doe	John F. Doe	John Frank Doe Robbins
4	John F Doe	John Doe	John F. Doe	John Frank Doe Robbins
5	John Frank Doe Robbins	John Doe	John F. Doe	John Frank Doe Robbins
6	John Frand Doe	John Doe	John F. Doe	John Frank Doe Robbins
7	John D Doe R	John Doe	John F. Doe	John Frank Doe Robbins
8	John F. Doe	John Doe	John F. Doe	John Frank Doe Robbins
9	John Doe	John Doe	John F. Doe	John Frank Doe Robbins
10	Doe, John	John Doe	John F. Doe	John Frank Doe Robbins
Start

To consider:
- Showing above only related strings, column has hundreds/thousands in random order
- Most of the values have more than 1 string within the cell
- ","(commas)
- "."(points)
- " "(single spaces)
- " "(multiple spaces or other)

It might be easier to get the following output based on shorter and longer matches....:

finding matches.xlsx
	L	M		O
2	Original	Proposed 1		Proposed 3
3	John Doe	John Doe		John Frank Doe Robbins
4	John F Doe	John Doe		John Frank Doe Robbins
5	John Frank Doe Robbins	John Doe		John Frank Doe Robbins
6	John Frand Doe	John Doe		John Frank Doe Robbins
7	John D Doe R	John Doe		John Frank Doe Robbins
8	John F. Doe	John Doe		John Frank Doe Robbins
9	John Doe	John Doe		John Frank Doe Robbins
10	Doe, John	John Doe		John Frank Doe Robbins
Start

Thank you, and have a great and safe weekend!!!

 

[Rijnsent]

Try the Fuzzy Lookup download from Microsoft: Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center
Cheers,
Koen

 

[andnand]

Hi Koen,

Thank you for the suggestion but I need to automate the task using vba....

Cheers,

Andrew

 

[Rijnsent]

Hi Koen,

Thank you for the suggestion but I need to automate the task using vba....

Cheers,

Andrew

Hi Andrew,
you forgot to add your efforts writing that code so far (this forum is not a free coding service but full of enthusiastic volunteers that can help you). Searching for "VBA fuzzy lookup" gets you e.g. this code Excel VBA module for fuzzy lookup (via Levenshtein distance) for a person id by first name and last name in a person range that has first, last, full and id fields. which can be helpful.
Koen

 

[andnand]

Hi Koen,

I was looking for guidance because my efforts didn't pay off. I simply clean up, replacing " " "," & "." helped a lot but then you have tons with strings at the end and strings in the middle. Even when I tried to replace the characters it took forever, selecting the column and replacing will replace everywhere and creating a simple cell by cell but that took forever so I ended up copying the range to an array and clean the elements of the array and pasting them back to the range...

I have seen the link you recommended above, is too complicated for my level...

Thank you and please let me know if there is some other guidance I can follow.