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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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,203

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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,803
Messages
5,655,388
Members
418,195
Latest member
LabraLime

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