Match multiple substrings in a cell against a lookup table and return concatenated values

**stevewc** · 05-13-2013, 09:12 AM

Hi,

I wonder if anyone can help? I'm working with data that needs a complicated lookup and replace. I've tried various versions of vlookup formula but with no luck and was wondering if you guys could help me out?

I have a column that has comma separated values of text. I need to replace these with different text strings. I have a lookup worksheet with the replacements. If this were a simple one for one lookup it'll be simple with vlookup. But I can't find a way to get it to work with substrings.

For example, the row may be:

"^14_eng_6^,^19_ips_3^,^19_ips_7^,^32_uti_3^"

and I have a lookup worksheet like:

"^14_eng_6^" : "^C_int_8^"
"^19_ips_3^" : "^D_abs_6^"
"^19_ips_7^" : "^C_int_7^"
"^32_uti_3^" : "^C_int_2^"

I would want a function that would return a cell value like:

"^C_int_8^","^D_abs_6^","^C_int_7^","^C_int_2^"

To make matters worse, the ideal solution would remove any duplication of new values as the lookup table could allow the same result for different lookup values. Eg:

^1_eng_7^ : ^C_eng_5^
^1_eng_8^ : ^C_eng_5^

I'll attach a sample document.

I know it's a bit of a complicated one, but if anybody could point me in the right direction with functions to use, it'll be really, rellay appreciated.

sample_example.xlsx

Thanks,
Steve

**DonkeyOte** · 05-13-2013, 09:54 AM

Steve,

Welcome to the Board.

Excel is not great when it comes to working with string Arrays so I would suggest you go down the UDF route - one example might be:

Function ReplaceStr(rngOrig As Range, rngReplace As Range, Optional strDelim As String = ",") As Variant
    Dim vTemp As Variant
    Dim lngR As Long
    vTemp = Split(rngOrig(1), strDelim)
    For lngR = LBound(vTemp) To UBound(vTemp) Step 1
        On Error Resume Next
        vTemp(lngR) = Application.VLookup(vTemp(lngR), rngReplace, 2, 0)
        On Error GoTo 0
        If Not IsError(vTemp) Then
            If InStr(1, ReplaceStr, vTemp(lngR), vbTextCompare) = 0 Then
                ReplaceStr = ReplaceStr & strDelim & vTemp(lngR)
            End If
        End If
    Next lngR
    ReplaceStr = Replace(ReplaceStr, ",", "", 1, 1)
End Function

The above would be stored in a Standard Module in VBE - once in place (and file stored as either .xls or .xlsm) you can then use like any other function though macros would need to be enabled - example usage:

Formula:



B1:

=REPLACESTR(A1,lookups!$A$2:$B$18)

There are numerous approaches to this type of issue -- though 90% will be using VBA for sake of efficiency.

**jindon** · 05-13-2013, 09:59 AM

UDF

Use in cell like

=VLookUps(A2,lookups!$A$2:$B$18,2)

To a standard module

Function VLookUps(txt, rng As Range, ref As Long) As String
    Dim dic As Object, r As Range
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    For Each r In rng.Columns(1).Cells
        If InStr(1, txt, r.Value, 1) <> 0 Then dic(r(, ref).Value) = Empty
    Next
    VLookUps = Join(dic.keys, ",")
End Function

**stevewc** · 05-13-2013, 10:50 AM

Thank you - you are both stars!

Steve

Match multiple substrings in a cell against a lookup table and return concatenated values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Match multiple substrings in a cell against a lookup table and return concatenated values

Re: Match multiple substrings in a cell against a lookup table and return concatenated val

Re: Match multiple substrings in a cell against a lookup table and return concatenated val

Re: Match multiple substrings in a cell against a lookup table and return concatenated val

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions