Reputation: 199

Concatenate cell data into another data if values matches

I have two columns A and B in same excel sheet. I am trying that if in Column B two values matches then it should copy related value A in same row.

For e.g

Table

Column A      Column B
xyz              1
abc              1
pqr              1  
eee              2
qqq              3
www              4
oop              5

Desierd Output

column A         Column B
xyz,abc,pqr         1
eee                 2
qqq                 3
www                 4
oop                 5

Upvotes: 2

Answers (3)

Scott Holtzman

Reputation: 27269

There's a formula solution for this as well (with helper columns):

Assuming data is column A:B ...

In C1 write this formula: =IF(A1<>A2,B2,D1&","&B2)
In D1 write this formula: =IF(A2<>A3,A2,"")
Filter on column D for blanks and then deleted the visible cells.

Upvotes: 0

R.Katnaan

Reputation: 2526

You can also use this one:

Public Sub combine()

    Dim row, result, lastRow As Integer
    Dim isExist As Boolean

    With Sheets("sheetname")

        'get the last use row
        lastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).row

        'Loop from row 1 to last row
        For row = 1 To lastRow Step 1

            'set the start row for result.
            result = 1

            'Reset flag
            isExist = False

            'Loop result count column until blank
            Do While .Range("F" & result) <> ""

                'check count
                If .Range("B" & row) = .Range("F" & result) Then

                    isExist = True

                    'If old, combine
                    .Range("E" & result) = .Range("E" & result) & "," & .Range("A" & row)

                    Exit Do

                End If

                'increase row
                result = result + 1

            Loop

            'If new, add new record
            If Not isExist Then
                .Range("E" & result) = .Range("A" & row)
                .Range("F" & result) = .Range("B" & row)
            End If

        Next row

    End With

End Sub

Here, testing evidence for my code:

I used column A & B as input and column E & F as output.

If there is any problem, let me know.

Upvotes: 2

user4039065

Reputation:

You could probably use a User Defined Function (aka UDF) for this. Put this into a module sheet.

Public Function conditional_concat_strs(rSTRs As Range, rCRITs As Range, rCRIT As Range, Optional sDELIM As String = ", ")
    Dim c As Long, sTMP As String

    Set rSTRs = rSTRs.Cells(1, 1).Resize(rCRITs.Rows.Count, rCRITs.Columns.Count)
    For c = 1 To rCRITs.Cells.Count
        If rCRITs(c).Value2 = rCRIT Then _
            sTMP = sTMP & rSTRs(c).Value & sDELIM
    Next c
    conditional_concat_strs = Left(sTMP, Application.Max(Len(sTMP) - Len(sDELIM), 0))
End Function

Use like any native worksheet function.

Upvotes: 6

Concatenate cell data into another data if values matches

Answers (3)

Related Questions