Lookup multiple values in a single cell (separated by commas) and then return the values

[luizeba]

Sorry if that's a bit long winded...Here's how it breaks down.

I've been just hired to an office where they have some cells with several values in them, separated by comma. Something like that:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]Companies[/TD]
[TD="align: center"]Names[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Albert, Bob[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A, B[/TD]
[TD="align: center"]Charlie[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]B, C, D[/TD]
[TD="align: center"] Albert, Charlie[/TD]
[/TR]
</tbody>[/TABLE]



And now I need to list, in another table, the Companies and every ID they are involved with; in another table, all names and every ID they are involved with:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Companies[/TD]
[TD="align: center"]ID 1[/TD]
[TD="align: center"]ID 2[/TD]
[TD="align: center"]ID 3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm not an expert with Excel, so I'm sorry if it's kinda obvious or easy to do.


Thanks for the help, guys

 

[Eric W]

Welcome to the forum.

As it's set up, it's not particularly easy to do. You could select column B, and from the Data tab, click on Text to Columns, and that would split out the companies into different columns. Once that's done, you could conceivably come up with some formulas to generate the table you want. But those formulas would be fairly complicated.

Another option would be to write a macro to build your table, which I did. Here's how to use it:

1) Open your workbook to the page with the first table
2) Right click the sheet tab on the bottom and select View Code
3) From the VBA Editor menu, click Insert > Module
4) On the sheet that opens, paste this code:

Option Explicit


Public Sub GetIDs()
Dim MyDict As Object, r As Long, c As Long, i As Long
Dim InRange As Variant, OutRange As Range, k, Comps, IDs, MyID

    Application.ScreenUpdating = False
    
    InRange = Sheets("Sheet4").Range("A2:C4")
    Set OutRange = Sheets("Sheet5").Range("A:E")
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    
    OutRange.ClearContents
    
    For r = 1 To UBound(InRange)
        Comps = Split(InRange(r, 2), ",")
        MyID = InRange(r, 1)
        For i = 0 To UBound(Comps)
            k = Trim(Comps(i))
            MyDict.Item(k) = MyDict.Item(k) & "," & MyID
        Next i
    Next r
    
    OutRange.Cells(1, 1) = "Companies"
    For c = 1 To OutRange.Columns.Count - 1
        OutRange.Cells(1, c + 1) = "ID " & c
    Next c
    
    r = 2
    For Each k In MyDict
        OutRange.Cells(r, 1) = k
        IDs = Split(Mid(MyDict.Item(k), 2), ",")
        For c = 0 To WorksheetFunction.Min(UBound(IDs), OutRange.Columns.Count - 2)
            OutRange.Cells(r, c + 2) = IDs(c)
        Next c
        r = r + 1
    Next k

    k = OutRange.Address(, , , 1)
    k = Mid(k, InStr(k, "]") + 1)
    k = Left(k, InStr(k, "!") - 1)
    
    With Worksheets(k).Sort
        .SetRange Range(OutRange.Address)
        .Header = xlYes
        .Apply
    End With
    
End Sub

5) Set the input and output ranges (in red) to match your worksheet. Note that the input range skips the heading row. Also, the output range is just 5 columns. If you expect more than a maximum of 4 IDs for a single company, make it wider.
6) Press Alt-Q to close the VBA editor.
7) Back in Excel, press Alt-F8 to open the macro selector. Select GetIDs and click Run.

Let me know how that works.

Edit: This macro use the Dictionary object, which doesn't work on Macs. If you have a Mac, let me know and I'll revise it.