CODEWITHSUNDEEP
excelvbavlookup
GTGabaaron
GTGabaaron

Reputation: 129

Dynamic Lookup for multiple values in a cell (comma separated) and return the corresponding ID to a single cell (comma separated also)

The thing is not always the amount of values (IDs) will be the same within each cell (at least 1, max=several) that's why the fixed version of using concatenated vlookup+left/mid/right will not work for me due to that will solution will only work up to 3 values. The only fixed size is the size of the values to lookup (IDs - in green), 8 characters (letters+numbers).

I'm not sure but, is it possible to setup a loop within excel formulas/functions ? Below is a table containing an example of the issue I'm trying to resolve and the expected values (tables are in different tab). Hope you can help. Thanks.

example-tables enter image description here

Upvotes: 0

Views: 2286

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

If you have windows Excel O365 with the TEXTJOIN and FILTERXML functions, you can use a formula:

=TEXTJOIN(",",TRUE,IFERROR(XLOOKUP(FILTERXML("<t><s>" & SUBSTITUTE(@[IDs],",","</s><s>") & "</s></t>","//s"),Table2[IDs],Table2[IDv2]),"""--"""))

Note that, in your data, there are two ID's in A4 that do not match any ID's in Table 2. Although that may be a typo, I left them as is to demonstrate the error handling.

Table1 enter image description here

Table2

enter image description here

Upvotes: 1

Elbert Villarreal
Elbert Villarreal

Reputation: 1716

Try this: 

Option Explicit

    Sub Cell2List()
        Dim wF As WorksheetFunction: Set wF = Application.WorksheetFunction 'To user Transpose
        Dim i As Range
        Dim j As Range
        Dim s As String: s = "," 'The separator of the list

        'Ask the user for the cell where are the list with the commas
        'Just need to select the cell
        Set i = Application.InputBox("Select just one cell where the values are", "01. Selecte the values", , , , , , 8)

        'Ask the for the separator. If you are completely sure the comma will never change just delete this line
        s = Application.InputBox("Tell me, what is the character separator, just one character! (optional)", "02. Separator (comma semicolon colon or any other char)", , , , , , 2)
        If s = "" Then s = "," 'Verifying...........

        'Ask the user where want to put the list
        'You need to get ready the cells to receive the list.
        'If there any data will be lost, the macro will overwrite anything in the cells
        Set j = Application.InputBox("Select just one cell where the values will go as a list, just one cell!", "03. Selecte the cell", , , , , , 8)

        Dim myArr: myArr = (Split(i.Value, s)) 'Split the list into a Array


        Range(Cells(j.Row, j.Column), Cells(j.Row + UBound(myArr), j.Column)).Value = wF.Transpose(myArr)
        'j.Row is the row of the cell the user selected to put the cell
        'j.Column the same, but the column
        'j.Row + UBound(myArr) =      UBound(myArr) is the total count of elements in the list
        '                            +j.Row
        '                            _______________
        '                            the last cell of the new list!
        'wF.Transpose(myArr) = we need to "flip" the array... Don't worry, but Don't change it!
    End Sub

You can put this macro with a button tin the ribbons, or use it as you can see in the gif

enter image description here

And this will be the result: (with a bigger list)

enter image description here

EDIT

You can use this UDF: 

Function Cells2List(List As Range, Pos As Integer) As String
    Cells2List = Split(List, ",")(Pos - 1)
End Function

Just need to define and index this way:

enter image description here

To tell the function, what index you want to see. You can use the function using ROW()-# to define an 1 at the beginning and when the formula send a #VALUE! delete the formulas. Where $A$1 is where the list are, and D7 is where the index are.

Upvotes: 0

Variatus
Variatus

Reputation: 14373

Here is a UDF that will do what you describe. Paste the code into a standard code module (not one already existing in the workbook but one that you create and that would have a name like Module1 before you change it to what you like best. You can also rename the function to give it a more suitable name.

Function ID_v2(Cell As Range) As String
    ' 035

    Dim Fun         As String           ' function return value
    Dim Sp()        As String           ' array of CSVs of CellVal
    Dim VLRng       As Range            ' the lookup range
    Dim VL          As Variant          ' result of VLookup
    Dim i           As Integer          ' loop counter

    ' this is a range similar to your sample A10:D19
    Set VLRng = ThisWorkbook.Names("Table2").RefersToRange
    Sp = Split(Cell.Cells(1).Value, ",")
    If UBound(Sp) >= 0 Then
        For i = 0 To UBound(Sp)
            On Error Resume Next
            VL = Application.VLookup(Trim(Sp(i)), VLRng, 3, False)
            If Err Then VL = "[ERROR]"
            Fun = Fun & VL & ","
        Next i
        ID_v2 = Left(Fun, Len(Fun) - 1)      ' remove final comma
    End If
End Function

Call the function with syntax like built-in functions. For example,

= ID_v2(A3)

This can be copied down like any other function. But remember to save the workbook as macro-enabled.

Upvotes: 0

Related Questions