CODEWITHSUNDEEP
excelvba
raym0nde
raym0nde

Reputation: 17

Populate Column B with abbreviations for string in Column A

I have an excel workbook with two sheets, on Sheet2:ColumnA is a list of words, one per row and Sheet2:ColumnB is their abbreviations. Sheet1:ColumnA is a string of words space delimited.

Sheet1:ColumnC is a prefix string and Sheet1:ColumnD is a postfix. I would like to populate Sheet1:ColumnB with the concatenation of Sheet1:ColumnC, the abbreviations from Sheet2:ColumnB and Sheet1:Column D.

e.g.

Sheet1:ColumnA = Gold Silver Bronze

Sheet1:ColumnC = ABC

Sheet1:ColumnD = 123

Sheet2:ColumnA

Row1: Gold

Row2: Silver

Row3: Iron

Sheet2:ColumnB

Row1: Au

Row2: Ag

Row3: Fe

Sheet1:ColumnB = ABCAuAgXX123 (where XX is used for undefined items)

Thanks for any assistance.

Upvotes: 0

Views: 95

Answers (1)

VBasic2008
VBasic2008

Reputation: 54863

A Concat UDF

  • In the worksheet Sheet1 in cell B1 use the following formula:

      =ConcElements(C1,A1,D1)

  • Copy the following function into a standard module, e.g. Module1.

Option Explicit

Function ConcElements( _
    ByVal FirstString As String, _
    ByVal ElementsString As String, _
    ByVal ThirdString As String) _
As String
    'Application.Volatile
    
    Dim Elements() As String: Elements = Split(ElementsString)
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Sheet2")
    
    Dim eIndex As Variant
    Dim eString As String
    Dim e As Long
    
    For e = 0 To UBound(Elements)
        eIndex = Application.Match(Elements(e), sws.Columns(1), 0)
        If IsError(eIndex) Then
            eString = eString & "Xx"
        Else
            eString = eString & sws.Cells(eIndex, 2).Value
        End If
    Next e
   
    ConcElements = FirstString & eString & ThirdString

End Function

Upvotes: 0

Related Questions