trixits
asked on
I wish to find keywords across multiple columns in a single row and then list them in a single cell in comma separated values...
Hi Excel Wizards...
I've got the following table with an Image ID followed by columns (and various amounts) of keywords:
(this table is huge and cannot be reformatted)
IMAGE ID KEYWORDS
00101-80001-16 -- Ocean Sand -- Environment Nature -- 060315 --
01200-30052-70 -- Hawaiian -- Hawaiian Culture Cultural -- Printscapes -- -- Fine Art --
10018-30194-76 -- Ocean Sand -- Environment Nature -- Printscapes -- -- Fine Art --
10071-30455-16 -- Health Beauty -- Concept Oahu Hawaiian Islands Pacific
I've also got a column of predefined words I need to find (on a separate sheet for now, this can be reformatted into rows or csv if need be):
-- Ocean Sand --
Nature
-- Fine Art --
OBJECTIVE: I would like to add a column called LIST that can displays predefined words (if they exist) as comma separated values in a single cell.
The output would appear like so:
IMAGE ID LIST
00101-80001-16 -- Ocean Sand --, Nature
01200-30052-70 -- Fine Art --
10018-30194-76 -- Ocean Sand --, Nature, -- Fine Art --
10071-30455-16
HELP! Mahalo for your time!
Krissy
I've got the following table with an Image ID followed by columns (and various amounts) of keywords:
(this table is huge and cannot be reformatted)
IMAGE ID KEYWORDS
00101-80001-16 -- Ocean Sand -- Environment Nature -- 060315 --
01200-30052-70 -- Hawaiian -- Hawaiian Culture Cultural -- Printscapes -- -- Fine Art --
10018-30194-76 -- Ocean Sand -- Environment Nature -- Printscapes -- -- Fine Art --
10071-30455-16 -- Health Beauty -- Concept Oahu Hawaiian Islands Pacific
I've also got a column of predefined words I need to find (on a separate sheet for now, this can be reformatted into rows or csv if need be):
-- Ocean Sand --
Nature
-- Fine Art --
OBJECTIVE: I would like to add a column called LIST that can displays predefined words (if they exist) as comma separated values in a single cell.
The output would appear like so:
IMAGE ID LIST
00101-80001-16 -- Ocean Sand --, Nature
01200-30052-70 -- Fine Art --
10018-30194-76 -- Ocean Sand --, Nature, -- Fine Art --
10071-30455-16
HELP! Mahalo for your time!
Krissy
ASKER
Wow, Glenn. That was very fast! Thanks for the comment...
I'm sorry, I should have also mentioned that I'm a noob and I have no idea how to use what you've given me above.
How do I use it?
I'm sorry, I should have also mentioned that I'm a noob and I have no idea how to use what you've given me above.
How do I use it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OH MY GAWD...
KEVIN, THANK YOU! You are a Experts-Exchange GENIUS!
How much I love you I cannot explain. Bless your beautiful mind. :)
(Glenn, thanks for your effort too!)
KEVIN, THANK YOU! You are a Experts-Exchange GENIUS!
How much I love you I cannot explain. Bless your beautiful mind. :)
(Glenn, thanks for your effort too!)
I can die happy now.
The following code checks for matches and concatintes the text palced in column B:
For r2 = 2 To wsssource 'Cycling through the data for the update components
For r1 = 2 To wsssmatch 'cycling through the taxonomy to find matches
If Trim(wsMatch.Cells(r1, 3)) = Trim(wsSource.Cells(r2, 7)) And Len(Trim(wsSource.Cells(r2
Sheets("Update").Select 'generates the first part of the Update component
wsUpdate.Cells(r3, 1) = wsSource.Cells(r2, 3)
wsUpdate.Cells(r3, 2) = wsMatch.Cells(r1, 1) & "/" & wsSource.Cells(r2, 8) & "/A "
r5 = r1 + 1
r6 = r2 + 1
mxxx: Do While wsSource.Cells(r2, 3) = wsSource.Cells(r6, 3) 'checks the next record for matching email address
For r5 = 2 To wsssmatch 'cycles through to find matches
If Trim(wsMatch.Cells(r5, 3)) = Trim(wsSource.Cells(r6, 7)) And wsSource.Cells(r2, 3) = wsSource.Cells(r6, 3) And r5 < wsssmatch Then
wsUpdate.Cells(r3, 2) = wsUpdate.Cells(r3, 2) & wsMatch.Cells(r5, 1) & "/" & wsSource.Cells(r6, 8) & "/A " 'cancatinates the additional matching components
r6 = r6 + 1
r5 = 1 'finished the cancatination of the record, restart the taxonomy at the beginning for the next individual.
End If
If r5 = wsssmatch Then 'If the skill does not match, go to the next one
r6 = r6 + 1: GoTo mxxx: End If
Next r5
Loop 'checks for next matching email address
r3 = r3 + 1
End If
Do While Len(Trim(wsSource.Cells(r2
r2 = r2 + 1: r6 = r6 + 1 'increments both counters used with wsSource
Loop
If r1 = wsssmatch Then 'If the first record of the group does not have a match, increment to the next data record
r2 = r2 + 1: r6 = r2: End If 'Trick the counters to reset appropriately
r2 = r6
Next r1
On Error GoTo handlecancel
Next r2
handlecancel: Sheets("Directions").Selec
Glenn