Is it possible in excel?

[phyro]

Hello everyone ,

I would like to ask if it is possible in excel to have a list ( description , code etc ) and in an other list a description similar to the first list description but written differently , compare them and try to find which item from the second list matches which on the first.

e.g.

1st list

No. Desc.
001 music videos with english subtitles


2nd list


Desc.

english music videos


Please i always think i m close to the solution but nothing yet

 

[MartyS]

Hello everyone ,

I would like to ask if it is possible in excel to have a list ( description , code etc ) and in an other list a description similar to the first list description but written differently , compare them and try to find which item from the second list matches which on the first.

e.g.

1st list

No. Desc.
001 music videos with english subtitles


2nd list


Desc.

english music videos


Please i always think i m close to the solution but nothing yet

How about this: https://www.dropbox.com/s/t8hjlmh5qhvi2i7/MessyMatch.xlsm?dl=0



All I've done here is split out the component parts of one string - and have it appear in an array of substrings to be used with the INSTR function.

I've then done a Tally on how many times it appears.

Then I've gone onto the next line of text down... split that into an array of substrings to search for.. counted a tally and so on...

Once all the way through the list of substrings, we start on the next item in the main list.. rinse:wash:repeat!

Only the highest tally is recorded.

Let's say the highest number of words that match are 4..

If there's more than one match with 4 words, all records with 4 matches are returned (along with the number of matches = 4)

Finally, it's displayed in an easily accessible / readable grid.


Hope that (the spreadsheet) gives you something to play with and generate more ideas from!

 

[MartyS]

A slightly updated version with a conditional formatting "cross-reference" on right-hand-side!

https://www.dropbox.com/s/184a12y6d1r6l8h/MessyMatch2.xlsm?dl=0

 

[phyro]

Thank you very much MartyS ,

The part with the instr was what i was thinking , i will study the code give you my impressions and again thank you for your time spent to help me .

 

[Aladin Akyurek]

Do also a search on "fuzzy matching" on this board.

 

[phyro]

Thank you Aladin Akyurek ...

MartyS solution works fine and now i have to make some modifications and and lots of studing before that.. hope i ll find some quality time to do so

 

[MartyS]

Thank you Aladin Akyurek ...

MartyS solution works fine and now i have to make some modifications and and lots of studing before that.. hope i ll find some quality time to do so

Thank you Phyro!

Good luck with your studying.