E
erighter
I'm trying to figure out a way to tell if any combination of the words
(single space separated) in List A exactly matches any combination of the
words in List B (including only matching the exact number of words)
I would be happy to use either Excel or Access to work on this...
List A
--------------
row 1: apple peach pear
row 2: bear dog cat
row 3: jump rope
List B
--------------
row 1: rope jump
row 2: cat bear dog
row 3: pear dog porcupine
row 4: apple peach pear grape
End Results:
--------------
(List B)
row 1: rope jump (matches Column A row 3)
row 2: cat bear dog (matches Column A row 2)
row 3: pear dog porcupine (no match)
row 4: apple peach pear grape (no match - too many words in Column B)
My idea so far is to do count the words in each column so that I will know
if the match is invalid because the number of words in each comparison has to
be equal (each string of multiple words uses a single space delimiter).
Then I was trying to find a way to put each word (substring) into it's own
column, because I think that would allow me to use the Excel Match function
to compare a word agains the array of possible words....
Column A: Column B:
A B C A B C
----- ------- -------- ----- ------- ------
row1 : bear dog cat cat bear dog
If cat in Column B row 1 matched cat in Column A row 1, then incement a
counter.
If not, I need to compare to Column A row 2 and so forth.
Then the whole process repeats, matching Column B's bear to the array - row
by row.
In the end, if the counter matches the count of words, then we can determine
that there was a positive match made for that string in some combination.
This was just my initial idea on how to tackle this problem, but I can't
parse out the keywords into individual columns to try it.
All ideas are greatly appreciated. Thanks!
(single space separated) in List A exactly matches any combination of the
words in List B (including only matching the exact number of words)
I would be happy to use either Excel or Access to work on this...
List A
--------------
row 1: apple peach pear
row 2: bear dog cat
row 3: jump rope
List B
--------------
row 1: rope jump
row 2: cat bear dog
row 3: pear dog porcupine
row 4: apple peach pear grape
End Results:
--------------
(List B)
row 1: rope jump (matches Column A row 3)
row 2: cat bear dog (matches Column A row 2)
row 3: pear dog porcupine (no match)
row 4: apple peach pear grape (no match - too many words in Column B)
My idea so far is to do count the words in each column so that I will know
if the match is invalid because the number of words in each comparison has to
be equal (each string of multiple words uses a single space delimiter).
Then I was trying to find a way to put each word (substring) into it's own
column, because I think that would allow me to use the Excel Match function
to compare a word agains the array of possible words....
Column A: Column B:
A B C A B C
----- ------- -------- ----- ------- ------
row1 : bear dog cat cat bear dog
If cat in Column B row 1 matched cat in Column A row 1, then incement a
counter.
If not, I need to compare to Column A row 2 and so forth.
Then the whole process repeats, matching Column B's bear to the array - row
by row.
In the end, if the counter matches the count of words, then we can determine
that there was a positive match made for that string in some combination.
This was just my initial idea on how to tackle this problem, but I can't
parse out the keywords into individual columns to try it.
All ideas are greatly appreciated. Thanks!