Transform comma separated ID's into comma separated Names in Excel 2007/10 using lookup table

[BradleyS]

I'm trying to match comma separated ID Data to the LOOKUP table and return the NAMEs of the IDs also as comma separated. The last column shows what the required output should look like, and the Current Formula Name Output is the results I have from my formula, which is working correctly.

- Note that if there is no such ID it should just ignore it as in S4.
- The IDs can vary up to 10 characters in length and contain symbols
- The Name can be up to 256 characters in length
- The amount of comma separated ID's in the DATA column cells can be up to 100.

I'm basically trying to transform the ID DATA into each of the LOOKUP NAMES

The solution can be in either VBA or an Excel formula, whichever is easier. I'm not very good at either, but my formula is the best I have come up with, although you can see it has problems with missing ID's and only checks 3 ID's.

My current formula that isn't working:=VLOOKUP(TRIM(LEFT(SUBSTITUTE(D2,",",REPT(" ",20)),20)),$A$2:$B$8,2,FALSE)&","&VLOOKUP(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",20)),20,20)),$A$2:$B$8,2,FALSE)&","&VLOOKUP(TRIM(RIGHT(SUBSTITUTE(D2,",",REPT(" ",20)),20)),$A$2:$B$8,2,FALSE)

Any help would be most appreciated to achieve the required output result.

Example table below

LOOKUP ID	LOOKUP NAME	*	ID DATA	Current Formula NAME OUTPUT	Required NAME OUTPUT
S2	ABCDE	*	S2,S6,S7,S4,S3	ABCDE,ADE,AB	ABCDE,ADE,AB
S3	AB	*	G2,S4	#N/A	#N/A
S6	ADE	*	G9,S6,G2,S3	GPES,ADE,AB	GPES,ADE,AB
S7	!TE	*	G2,G9	D,GPES,GPES	D,GPES,GPES
G2	D	*	G2	#N/A	#N/A
G9	GPES	*	S2,G9	ABCDE,GPES,GPES	ABCDE,GPES,GPES
&ALL	ALL	*	&ALL	#N/A	#N/A

 

[kweaver]

Why isn't S7 matched with ITE in the first row of your example? Numerous others are confusing.

 

[BradleyS]

Why isn't S7 matched with ITE in the first row of your example?

Good point I missed it out when manually entering the text example. My apologies.

 

[BradleyS]

The output result was completely wrong, as I have been trying all sorts of things to get this working and forgot to amend it when posted. Here is the correct output.

LOOKUP ID	LOOKUP NAME	*	ID DATA	Formula NAME OUTPUT	Required NAME OUTPUT
S2	ABCDE	*	S2,S6,S7,S4,S3	ABCDE,ADE,AB	ABCDE,ADE, !TE, AB
S3	AB	*	G2,S4	#N/A	D
S6	ADE	*	G9,S6,G2,S3	GPES,ADE,AB	GPES,ADE,D,AB
S7	!TE	*	G2,G9	D,GPES,GPES	D,GPES
G2	D	*	G2	#N/A	D
G9	GPES	*	S2,G9	ABCDE,GPES,GPES	ABCDE,GPES
&ALL	ALL	*	&ALL	#N/A	ALL

 

[kweaver]

How about this macro?

Sub SplitNames()
Dim ID() As String, REQ As String, r As Variant, i As Long, j As Long, LR As Long, ub As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Err.Clear
For i = 2 To LR
  ID = Split(Range("D" & i), ",")
  ub = UBound(ID())
    For j = 0 To ub
    On Error Resume Next
    r = Application.WorksheetFunction.VLookup(ID(j), Range("A2:B" & LR), 2, False)
    If Err.Number = 0 Then REQ = REQ & r & ","
    Next j
   Cells(i, 5) = Left(REQ, Len(REQ) - 1)  ' Result in column E
   REQ = ""
Next i
End Sub

 

[BradleyS]

WOW!! That's it.

Thank you so much for your help with this. I was getting extremely lost and getting no where fast.

 

[kweaver]

Glad to help.