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!

Id	Name	Active
2186	Eames Lounge and Ottoman White Ash	N
2397	Eames Lounge Chair & Ottoman / Black Leather / Walnut Veneer	N
2595	Eames Lounge Chair & Ottoman	N
3028	Eames Lounge Chair and Ottoman	Y

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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