Hi! Newbie here, so please be gentle...
I have two columns of data where all the cells are text strings. I want to be able to compare each cell in the Column 2 with all of the cells in Column 1 to find which cell from column 1 has the most words that match the cell in column 2. None of them will exactly match, but some will match more than others (will have more words that match). I want to find the cell that best matches (has the most matching words) with the selected cell in column 2 and then pull off data relevant to the best matching cell, much like a vlookup would if it was a perfect match between the cells. I have provided a table below to try and better explain:
<tbody>
</tbody>
The fourth entry under column 2 best matches (has the most words that match) with the first entry under column 1, therefore it has pulled "1" from Info 1 into Info 2.
I understand that the VBA Split function would at least turn the text string into identifiable words, but where to go from there I do not know...
Your help would be greatly appreciated!
I have two columns of data where all the cells are text strings. I want to be able to compare each cell in the Column 2 with all of the cells in Column 1 to find which cell from column 1 has the most words that match the cell in column 2. None of them will exactly match, but some will match more than others (will have more words that match). I want to find the cell that best matches (has the most matching words) with the selected cell in column 2 and then pull off data relevant to the best matching cell, much like a vlookup would if it was a perfect match between the cells. I have provided a table below to try and better explain:
Column 1 | Info 1 | Column 2 | Info 2 | ||
The dog chased the cat down the road | 1 | The man loved his dog | |||
<tbody> </tbody> | 2 | The dog and the cat shared the shoe | |||
<tbody> </tbody> | 3 |
<tbody> </tbody> | |||
<tbody> </tbody> | 4 |
<tbody> </tbody> | 1 |
<tbody>
</tbody>
The fourth entry under column 2 best matches (has the most words that match) with the first entry under column 1, therefore it has pulled "1" from Info 1 into Info 2.
I understand that the VBA Split function would at least turn the text string into identifiable words, but where to go from there I do not know...
Your help would be greatly appreciated!