Abbreviation Combination

[nikhil0311]

Hello People, hope you are doing well.

I am looking for your expert help.

Column F have product name & Column G have abbreviations of these product names. In column A we have multiple products which are present in mapping table i.e. Column F&G.

Based on this mapping table I want to populate product Abbreviations in column B using column A. I am also fine with UDF function.

abbrevation combination.xlsx
	A	B	C	D	E	F	G
1	Product	Abbreviation				Mapping Table
2	Payables	Pay				Product	Abbreviation
3	Receivables, Payables	Rec, Pay				Payables	Pay
4	Merchant services, FX, Card, Payables	MS, FX, Card, Pay				Receivables	Rec
5	FX, Payables	FX, Pay				Merchant services	MS
6	Card, Receivables	Card, Rec				FX	FX
7						Card	Card
Sheet1

 

[Tetra201]

If you are on Excel 365, try

TEXTJOIN(", ",1,XLOOKUP(TRIM(TEXTSPLIT(A2,",",,1)),TRIM($F$3:$F$7),$G$3:$G$7,"",0,1))

 

[lrobbo314]

A VBA solution.

Sub pSplit()
Dim r As Range:         Set r = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim LK() As Variant:    LK = Range("F3:G" & Range("G" & Rows.Count).End(xlUp).Row).Value2
Dim cmb As Object:      Set cmb = CreateObject("System.Collections.ArrayList")
Dim res As Object:      Set res = CreateObject("System.Collections.ArrayList")
Dim tmp() As String

For i = 1 To UBound(AR)
    tmp = Split(AR(i, 1), ", ")
    cmb.Clear
    For Each t In tmp
        For j = 1 To UBound(LK)
            If LK(j, 1) = t Then cmb.Add LK(j, 2)
        Next j
    Next t
    res.Add Join(cmb.toArray, ", ")
Next i

r.Offset(, 1).Value = Application.Transpose(res.toArray)

End Sub