Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse a space delimited string into unique columns
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse a space delimited string into unique columns
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 erighter wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Columns from space delimited list | Excel Discussion (Misc queries) | |||
space delimited files now tab delimited | Excel Discussion (Misc queries) | |||
Lost columns in formated text (space delimited) save | Excel Worksheet Functions | |||
space delimited!! | Excel Discussion (Misc queries) | |||
Import Pipe Delimited File, Parse out certian Fields, create new f | New Users to Excel |