One to Many lookup quandry

[Jlewis30]

Greetings,

I have a long list of Account IDs and "interests" that I need to use to validate data in another table, by that Account ID. For any given Account ID I can have from one to 15 Interests associated.

If I use vlookup I am only going to get the first match, but I need to report all of the interests of Contacts associated with each ID (in a single cell, preferably). I am wondering if there is a simple(ish) way that I can concatenate any Interest that matches the Account ID of a Contact in my table.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contact[/TD]
[TD]Account ID[/TD]
[TD]Interests[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]abc1234[/TD]
[TD]Jazz, Rock, Swing[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]def5678[/TD]
[TD]Swing, Bluegrass[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account ID[/TD]
[TD]Interest[/TD]
[/TR]
[TR]
[TD]abc1234[/TD]
[TD]Jazz[/TD]
[/TR]
[TR]
[TD]abc1234[/TD]
[TD]Rock[/TD]
[/TR]
[TR]
[TD]abc1234[/TD]
[TD]Swing[/TD]
[/TR]
[TR]
[TD]def5678[/TD]
[TD]Swing[/TD]
[/TR]
[TR]
[TD]def5678[/TD]
[TD]Bluegrass[/TD]
[/TR]
</tbody>[/TABLE]

I am about to get all crazy bad and use subtotals to transpose columns to rows (MANUALLY) and create the concatenated result in my lookup table, but it is like 150,000 rows. I want to either automate creating the combined lookup table, or figure out how to get a "one to many" lookup.

 

[Jlewis30]

Oh duh. Working in Excel 2010, Win 7

 

[yearnintolearn]

see edited response below

 

[yearnintolearn]

You get the data for "interests" in seperate columns by going to Data>Text to Columns>Delimted>Check Comma>finish

 

[yearnintolearn]

[TABLE="width: 604"]
<tbody>[TR]
[TD]Contact[/TD]
[TD]Account ID[/TD]
[TD="colspan: 2"]Interests[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]abc1234[/TD]
[TD]Jazz[/TD]
[TD] Rock[/TD]
[TD] Swing[/TD]
[TD]=COUNTA(C2:E2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]def5678[/TD]
[TD]Swing[/TD]
[TD] Bluegrass[/TD]
[TD] [/TD]
[TD]=COUNTA(C3:E3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]List of Account ID's[/TD]
[TD]List of Interests[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=VLOOKUP(G6,B$2:E$3,COUNTIF(G6:G$6,G6)+1,FALSE)[/TD]
[TD]=B2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=VLOOKUP(G7,B$2:E$3,COUNTIF(G$6:G7,G7)+1,FALSE)[/TD]
[TD]=IF(((VLOOKUP(G6,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G6,G6))),G6,INDIRECT("B"&MATCH(G6,B$2:B$3,1)+ROW($B$2)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=VLOOKUP(G8,B$2:E$3,COUNTIF(G$6:G8,G8)+1,FALSE)[/TD]
[TD]=IF(((VLOOKUP(G7,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G7,G7))),G7,INDIRECT("B"&MATCH(G7,B$2:B$3,1)+ROW($B$2)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=VLOOKUP(G9,B$2:E$3,COUNTIF(G$6:G9,G9)+1,FALSE)[/TD]
[TD]=IF(((VLOOKUP(G8,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G8,G8))),G8,INDIRECT("B"&MATCH(G8,B$2:B$3,1)+ROW($B$2)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=VLOOKUP(G10,B$2:E$3,COUNTIF(G$6:G10,G10)+1,FALSE)[/TD]
[TD]=IF(((VLOOKUP(G9,B$2:F$3,5,FALSE))>(COUNTIF(G$6:G9,G9))),G9,INDIRECT("B"&MATCH(G9,B$2:B$3,1)+ROW($B$2)))[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]