vba to align 2 columns that have similar (not exact) wording

Hi all,

Wish you all a merry Christmas and happy new year.

This time i am seeking your support to find a vba that align cells from 2 excel columns that have similar text, but not exact one. Some background: This happens because 2 different clients call a same document with different name: eg. list of home appliances 1.0 12dec2014 vs home appliances list 12-Dec-14, feedback from client 1jan2019 vs client feedback 1-1-2019. Sometimes the name can be much longer. So column A is how client A named the documents and column B is how client B named the documents. I need the vba to align cell from column A with the most similar cell from column B in order to check if version and date is the same. Sometimes i can have 20 documents, i.e. 20 rows., and in order to check that the documents were assigned the same version i would start with row 1 column A, scroll down column B to find the row which has the name assigned to that file by client B and check the version and date. Then i have to go to cell A2, scroll thru column B to find out which cell has the name of the document and then check if the version/date matches. I imagine this Vba picking up each word from cell A1 (avoid numbers and dates in this analysis as this will be dona manually once the cells are aligned), checking the presence of that word in all of the cell from column B, assigning a score to each cell of column B depending on if the word was found and the length of the word (if there is a match, the longer the word is, the larger should be the score assigned), then the cell from column B that gets the larger score should be sorted to be in the same row as the cell from column A being analyzed. Hope this makes sense.

All the best!

Pablo

Originally Posted by Joske920

Hi,

Give us an example workbook
with both the cases

1 sheet with the data unsorted
1 sheet with the data sorted

thx

Hi Joske920,

Thank you for you time. Please find attached the requested spreadsheet.

Regards,

Thank you Joske920 !

I appreciate your time and expertise! I'll test it for a couple of days and let you know.

Thank you again!

Originally Posted by Joske920

Hi,

After thinking not to do it.. I took it as a challenge because its not evident to make this in a few minutes

Please Login or Register  to view this content.

It may not be pretty but it does the job
(Not tested extensively)

I hope you can use this.. this was not easy to me..

Hi Joske920 !
It works wonderfully with the example I sent you. Great work. The problem is I've checked it with another scenario and had some issues. I've tried to replicate it by modifying a little bit the spreadsheet i sent to you. For example, if i copy 1 item from List A and paste it at the end of List B, I would think that the macro will necessary align them (as they are equal), but for some reason the macro didn't align those cells. Question: is the macro picking 1 item from List A, searching for best fit from list B and then reversely checking if that item form list A is the best fit for the selected item from list B? For example: macro finds best fit for cell A3 is B14. Does the macro check that best fit of item B14 is A3?
Thanks again!

Thanks Joske920! I will check it and let you know. Again i really appreciate your time and work.