Comparing 2 strings for matches in any order

[Guest]

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 valid 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/field, 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!

 

[Tim Ferguson]

I would be happy to use either Excel or Access to work on this...

Nah: use Perl..!


Tim F

 

[Dirk Goldgar]

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 valid 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/field, 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!

Hmm. You could write a function that sorts each string of words and
returns the sorted string. Then matching one list against the other
would be a matter of comparing the function's output for each row.

The function would receive a string (the unsorted list of words) and
return a string (the sorted list). It could use the Split function to
parse the unsorted string into an array, then use a simple sort algorthm
to sort that list (VB code for sorting arrays is easy to find on the
web), and then use the Join function to reassemble the sorted array into
a string.

 

[John Nurick]

Nah: use Perl..!

That was my thought ... but sometimes SQL is nearly as good. Wouldn't it
be possible to import the data into this table

tblWords
List*
Row*
Word*

and then post a question in m.p.a.queries?

Otherwise, I've got a little VBA function that executes a Windows
command line and returns whatever the command sent to STDOUT ... so
maybe Perl is the answer.