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

0
votes

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LOOKUP ID</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LOOKUP NAME</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ID DATA</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">Formula NAME OUTPUT</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Required NAME OUTPUT</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ABCDE</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S2,S6,S7,S4,S3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">ABCDE,ADE,AB</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ABCDE,ADE,AB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">AB</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2,S4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">#N/A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ADE</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G9,S6,G2,S3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">GPES,ADE,AB</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">GPES,ADE,D,AB</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">!TE</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2,G9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">D,GPES,GPES</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">D,GPES</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">#N/A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">G9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GPES</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S2,G9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">ABCDE,GPES,GPES</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ABCDE,GPES</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">&ALL</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ALL</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;">*</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">&ALL</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2DDDC;;">#N/A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ALL</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">TRIM(<font color="Red">LEFT(<font color="Green">SUBSTITUTE(<font color="Purple">D2,",",REPT(<font color="Teal">" ",20</font>)</font>),20</font>)</font>),$A$2:$B$8,2,FALSE</font>)&","&VLOOKUP(<font color="Blue">TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">D2,",",REPT(<font color="Teal">" ",20</font>)</font>),20,20</font>)</font>),$A$2:$B$8,2,FALSE</font>)&","&VLOOKUP(<font color="Blue">TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">D2,",",REPT(<font color="Teal">" ",20</font>)</font>),20</font>)</font>),$A$2:$B$8,2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />
2
excelvbaexcel-formula
Wrap each vlookup in an IFERROR(...,"") – Scott Craner

2 Answers

0
votes

Try the following (assuming your expected results in E2 are wrong):

enter image description here

Formula in E2:

=TEXTJOIN(",",1,IF(A$2:A$8=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(D2,"&","&amp;"),",","</s><s>")&"</s></t>","//s")),B$2:B$8,""))

Note1: This formula needs to be entered through CtrlShiftEnter

Note2: Since & is a special character in XPATH we need to "escape" it with a second SUBSTITUTE.

Note3: If you are interested in FILTERXML, you might find this interesting?

0
votes

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.

enter image description here

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