Is it possible in excel?

phyro

New Member
Joined
Feb 20, 2018
Messages
3
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

MartyS

Board Regular
Joined
Feb 12, 2018
Messages
197
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!
 

phyro

New Member
Joined
Feb 20, 2018
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

phyro

New Member
Joined
Feb 20, 2018
Messages
3
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
 

Forum statistics

Threads
1,137,353
Messages
5,680,995
Members
419,948
Latest member
Sbakker1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top