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

Glenn_Moore

You can create a subroutine that searches for matches and then cancatineates the values.
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, 3))) <> 0 Then 'Found a match
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, 3))) = 0 'skips all records without email address
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").Select 'Returning to the original worksheet
Glenn

trixits

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?

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trixits

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

zorvek (Kevin Jones)

I can die happy now.