Combine (slightly) differently spelled cells into 1 consolidated name

[laxbbombers21]

I've hit a rut trying to consolidate my data so hopefully someone can help! The issue I have is trying to consolidate multiple listings for the same product that have differently titled names. As an example, below shows 4 different product ID's and names. The 3rd column shows that only 1 item is active. All 4 have historical sales, but I want to consolidate this into 1 item name for analysis purposes. I need to develop a formula so the other three will know to use the Active Item's Name. I tried messing around with the Match function and the (-1,0,1) changes to see if I can get partial matches to a list of just the active items, but no luck. Any thoughts?

Thanks!

[TABLE="width: 554"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Id[/TD]
[TD]Name[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]2186[/TD]
[TD]Eames Lounge and Ottoman White Ash[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]2397[/TD]
[TD]Eames Lounge Chair & Ottoman / Black Leather / Walnut Veneer[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]2595[/TD]
[TD]Eames Lounge Chair & Ottoman[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]3028[/TD]
[TD]Eames Lounge Chair and Ottoman[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

You need a Phonetic matching algorithm such as SOUNDEX or METAPHONE.

The algorithm is designed to match nearly identical names, based on interpreting them phonetically, but there are some limitations.

This thread shows a VBA implementation of the SOUNDEX algroithm and links to a site describing it and some other Fuzzy Logic matching functions..
http://www.mrexcel.com/forum/excel-questions/592866-excel-word-pitch-music-note.html

More robust algorithms are discussed here: Using Fuzzy Matching to Search by Sound with Python | | InformIT
and in the links on that page.

And another implementation:
Fuzzy Matching Demo in Access

 

[laxbbombers21]

Thanks for the feedback Phil. I will check out the links you provided!

You need a Phonetic matching algorithm such as SOUNDEX or METAPHONE.

The algorithm is designed to match nearly identical names, based on interpreting them phonetically, but there are some limitations.

This thread shows a VBA implementation of the SOUNDEX algroithm and links to a site describing it and some other Fuzzy Logic matching functions..
http://www.mrexcel.com/forum/excel-questions/592866-excel-word-pitch-music-note.html

More robust algorithms are discussed here: Using Fuzzy Matching to Search by Sound with Python | | InformIT
and in the links on that page.

And another implementation:
Fuzzy Matching Demo in Access