none
Data Concatenation using vba RRS feed

  • Question

  • I have some data in A & B Column of Sheet1. I want to apply a concatenate function to Column B based on the data available in Column A

    If Column A is populated with XX for a record then Column C needs to be populated with Column B data + Column A

    But before we start with the concatenation I need one more validation in Column B data. Some of the data in Column B are in comma delimited form. So we need to split those values before we start concatenation function.

    Following is the format of my Input data (First row is header):

    Column A

    Column B

    1

    AA, BB

    2

    CC

    3

    DD,EE,FF

    4

    GG,HH

    Output needs to be populated in C Column as follows:

    Result

    Result

    1

    AA

    1

    BB

    2

    CC

    3

    DD

    3

    EE

    3

    FF

    4

    GG

    4

    HH

    Thanks

    Monday, August 3, 2015 8:04 AM

Answers

  • *** something like ****

    Sub concat()
    Dim r As Range
    Set r = Worksheets("Sheet1").Range("B2:B5")
    Dim v As Range
    Dim sourceCol, resultRow, resultCol As Integer
    sourceCol = 1
    resultRow = 2
    resultCol = 3

    Dim substr() As String

    For Each v In r

        substr = Split(v, ",")
        numpart = Sheet1.Cells(v.Row, sourceCol).Value
        For i = LBound(substr) To UBound(substr)
            Sheet1.Cells(resultRow, resultCol) = numpart & "  " & substr(i)
            resultRow = resultRow + 1
        Next
    Next
    End Sub

    • Proposed as answer by partholon Monday, August 3, 2015 9:58 AM
    • Marked as answer by Hobert P Monday, August 3, 2015 10:07 AM
    Monday, August 3, 2015 9:57 AM