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

Question

I'm trying to match comma separated ID Data to the LOOKUP table and return the NAMEs of the IDs also as comma separated.

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's into Names

The solution can be in either VBA or an Excel formula, whichever is easier. I not very good at either, but the 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.

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

Example table in the Code Snippet below

 A B C D E F
1 LOOKUP ID LOOKUP NAME * ID DATA Formula NAME OUTPUT Required NAME OUTPUT
2 S2 ABCDE * S2,S6,S7,S4,S3 ABCDE,ADE,AB ABCDE,ADE,AB
3 S3 AB * G2,S4 #N/A D
4 S6 ADE * G9,S6,G2,S3 GPES,ADE,AB GPES,ADE,D,AB
5 S7 !TE * G2,G9 D,GPES,GPES D,GPES
6 G2 D * G2 #N/A D
7 G9 GPES * S2,G9 ABCDE,GPES,GPES ABCDE,GPES
8 &ALL ALL * &ALL #N/A ALL
Sheet1


Worksheet FormulasCell Formula
E2 =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)

Ron Rosenfeld · Accepted Answer

In the formulas below, I used a Table with structured references for the lookup table, but you can convert that to regular addresses if you prefer.

If you have Excel O365 with the SEQUENCE function, you can use:

=TEXTJOIN(", ",TRUE,IFERROR(INDEX(Table3[NAME],MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),IF(SEQUENCE(99)=1,1,(SEQUENCE(99)-1)*99),99)),Table3[ID],0)),""))

If you have Excel 2016 with the TEXTJOIN function, you can use:

=TEXTJOIN(", ",TRUE,IFERROR(INDEX(Table3[NAME],MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),IF(seq=1,1,(seq-1)*99),99)),Table3[ID],0)),""))

where seq is a Named Formula that refers to: =ROW(INDEX($A:$A,1):INDEX($A:$A,99))

If you have an earlier version of Excel lacking the TEXTJOIN function, a VBA solution would be best, although you could do it with multiple helper columns.

For those with earlier versions of Excel, here is a VBA routine that should work efficiently.

Since there could be up to 100 matches in the ID DATA column, I thought it might be faster to read the lookup table into a dictionary, instead of looping through the arrays each time. (Previous testing has shown that for single matches, looping a VBA array is faster than Worksheetfunction.Match and Range.Find methods, but I have not tested this for multiple matches.)

Option Explicit

'Go to `tools-->references` in the menu above and
'Set a reference to Microsoft Scripting Runtime for Dictionary

'Since there can be up to 100 items in the list,
'probably faster to read the table into a dictionary
'than to loop through the list for each item

Function getNames(ID_DATA As String, lookup_Table As Range) As String
    Dim D As Dictionary
    Dim I As Long
    Dim S As String
    Dim V As Variant, tbl As Variant

    'delimiter in the output string
    Const sDelim As String = ", "

'read table into dictionary for lookup
tbl = lookup_Table
Set D = New Dictionary
    D.CompareMode = TextCompare
For I = 1 To UBound(tbl)
    'will throw error if there are duplicate ID's
    'but these should be corrected anyway
    D.Add Key:=tbl(I, 1), Item:=tbl(I, 2)
Next I

'create the output string
S = ""
For Each V In Split(ID_DATA, ",")
    S = S & IIf(D(V) = "", "", sDelim & D(V))
Next V

getNames = Mid(S, Len(sDelim) + 1)

End Function

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

Answers (2)

Related Questions

Transform comma separated ID&#39;s into comma separated Names in Excel 2017/10 using lookup table

Answers (2)

Related Questions

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