Concatenate cell data into another data if values matches
Solution 1:
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.
Solution 2:
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.