Parse a space delimited string into unique columns

[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 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!

 

[john.topley]

The following will parse a string (with one or more) embedded blanks.
Output is placed in consecutive columns starting in Column "c" in Row
"r" i.e in example below A1=apples, B1=pears, C1=oranges etc ....


Sub test()

Dim wsn As Worksheet
Dim srow As Integer, scol As Integer
Dim intext As String

intext = "apples pears oranges pineapples"

srow = 1 ' row for parsed data
scol = 1 ' start column for parsed data in srow
Set wsn = Worksheets("Sheet1") ' Worksheet for parsed data

Call ParseString(intext, wsn, srow, scol)

End Sub

---------------------------------------------------------------------------------Sub
ParseString(StringToParse, ws, r, c)

Dim j As Long

j = 1

Do While j > 0
j = InStr(1, StringToParse, " ") ' Find position of blank
If j = 0 Then
ws.Cells(r, c) = StringToParse ' single value or last
value
Else
If j <> 1 Then
ws.Cells(r, c) = Left(StringToParse, j - 1)
c = c + 1
End If
' Remove last parsed value from front of string ..and repeat loop
StringToParse = Mid(StringToParse, j + 1, Len(StringToParse) -
j)


End If

Loop

End Sub
-------------------------------------------------------------------------------


HTH