Comma Delimited column - lookup for each value

[ylafont]

New to forum, forgive me if this has been asked previously and I am not as proficient in Excel as i shcould be. here is my dilema.

I have a column with a comma delimited list. some rows have more than on value, lick such.

Sheet1, Column A
Allen Brooth <+19175551234>
Allen Brooth <+19175551234>, +15165551234, +13475551234
Allen Brooth <+19175551234>, Leslie Gray <+17185551234>
Allen Brooth <+19175551234>, Leslie Gray <+17185551234>, +18008571200

Sheet2 contains a listing of each of the entries listed in Sheet1 with their corresponding value, like so

Sheet2, Column A and B
Allen Brooth <+19175551234> UID001
+15165551234 UID002
+13475551234 UID003
Leslie Gray <+17185551234> UID004
+18008571200 UID005

Is there a built in function or one already out there that could be used the create a column with the corresponding values separated by a comma, as such

Allen Brooth <+19175551234> UID001

Allen Brooth <+19175551234>, +15165551234, +13475551234 ---> UID001,UID002,UID003

Allen Brooth <+19175551234>, Leslie Gray <+17185551234> ----> UID001, UID004

Any assistance is appreciated. thank you.

 

[vletm]

@ylafont
Upload a Sample File to get a quicker response

 

[ylafont]

I think this should do it. thankyou.

 

[bosco_yip]

Try,

1] Helper C1, copy across until blank :

=IF(COLUMNS($A1:A1)<=MATCH("zzzz",Sheet2!$A:$A)-1,COLUMNS($A1:A1),"")

2] Helper C2, copy across and down :

=IF(C$1="","",IF(ISNUMBER(FIND(INDIRECT("Sheet2!A"&C$1+1),$A2)),INDIRECT("Sheet2!B"&C$1+1),""))

3] "Group ID" B2, copy down :

=SUBSTITUTE(TRIM(CONCATENATE(C2," ",D2," ",E2," ",F2," ",G2," ",H2," ",I2," ",J2," ",K2," ",L2," ",M2," ",N2," ",O2," ",P2," ",Q2," ",R2," ",S2," ",T2," ",U2," ",V2," ",W2," ",X2," ",Y2," ",Z2," ",AA2," ",AB2," ",AC2," ",AD2," ",AE2," ",AF2," ",AG2))," ",", ")

4] Hide helper columns if required

Regards
Bosco