Performing a Lookup for delimited values in a single cell and CONCAT results?

user_18238074 0
Viewed 45

I need to be able to manipulate an Excel data set to consolidate the 'Security Point' based on what 'Class' people have assigned to them. To do this, I need to be able to Lookup each value of a delimited list within a cell, then concatenate their respective outputs in another cell.

For example, Jeff has Class 1 and Class 3, so using the Class to Security Mapping Table, the third column should populate with the Security Points A,B,C,A,F,G with a comma delimiter. The reason for this is I want to create a 1:1 relationship between a person and their class, so Jeff should have a new class combining his Security Points in Class 1 and Class 3.

Name Class New Security Points
Jeff Class 1,Class 3 A,B,C,A,F,G
Mary Class 1,Class 2 A,B,C,C,D,E,F

Class to Security Mapping:

Class Security Point
Class 1 A,B,C
Class 2 C,D,E,F
Class 3 A,F,G

I first went about trying to use an IF-based INDEX MATCH/XLOOKUP/VLOOKUP into a CONCAT, but it got messy real fast. I then tried to deploy a VBA-based solution, but ran into an issue with the below function throwing #VALUE! for some rows and not others and otherwise other rows just completely missing some Security Points, for whatever reason. I've validated it is not a formatting error or inconsistent spellings:

Function LookupConcat(r As String, lookupColumn As Range, lngOffset As Long) As String
Dim t, u As Long, c As Range, s As String
t = Split(r, ",")
For u = 0 To UBound(t)
    Set c = lookupColumn.Find(Trim(t(u)))
    If Not c Is Nothing Then s = s & c.Offset(, lngOffset - 1) & ","
Next
If Len(s) Then LookupConcat = Left(s, Len(s) - 1)
End Function

I'm wondering if there's a cleaner function I can utilize, or an otherwise good UDF/VBA-based solution.

user-defined-functions vba excel
1 Answers

A VBA Lookup UDF: Delimited Values

enter image description here

Function LookupSP( _
    ByVal ClassString As String, _
    ByVal LookupRange As Range, _
    Optional ByVal ClassColumn As Long = 1, _
    Optional ByVal SecurityPointColumn As Long = 2, _
    Optional ByVal ClassDelimiter As String = ",", _
    Optional ByVal SecurityPointDelimiter As String = ",", _
    Optional ByVal NotFoundString As String = "Not found") _
As String
    
    If Len(ClassString) = 0 Then Exit Function
    
    Dim Classes() As String: Classes = Split(ClassString, ClassDelimiter)
    
    Dim ccrg As Range
    Dim scrg As Range
    
    With LookupRange
        Set ccrg = .Columns(ClassColumn)
        Set scrg = .Columns(SecurityPointColumn)
    End With
    
    Dim rIndex As Variant
    Dim n As Long
    Dim spString As String
    
    For n = 0 To UBound(Classes)
        rIndex = Application.Match(Classes(n), ccrg, 0)
        If IsNumeric(rIndex) Then
            spString = CStr(scrg.Cells(rIndex).Value)
        Else
            spString = NotFoundString
        End If
        LookupSP = LookupSP & spString & SecurityPointDelimiter
    Next n
    
    LookupSP = Left(LookupSP, Len(LookupSP) - Len(SecurityPointDelimiter))
    
End Function
user_98140690
Related