Merging Cells

[Dez]

Hi I am trying to Merge cells from col C in col D if ReportID in col B are the same. Cella A1 to A7 have formula =IF(COUNTIF(B1,B2)=0,1,0).


A B C D
ReportCount ReportID Descrption MergedDescription
1 1 100 Black Black, White
2 0 100 White
3 1 101 Green Green
4 1 102 Red Red, Purple, Orange
5 0 102 Purple
6 0 102 Orange
7 1 104 Yellow Yellow

Cheers Dez...

 

[texasalynn]

try this

Sub MergeDesc()
Dim LR As Long, i As Long
Dim CData As String
LR = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
x = 1
For i = 2 To LR
    For j = i To LR
        If x = 1 Then
            CData = Cells(i, "C")
        End If
        If Cells(j, "B") = Cells(j + 1, "B") Then
            CData = CData & ", " & Cells(j + 1, "C")
            x = x + 1
        End If
        If Cells(j, "B") <> Cells(j + 1, "B") Or j + 1 > LR Then
            Cells(i, "D") = CData
            x = 1
            GoTo 0
        End If
    Next j
0
If j <> i Then
    i = j
End If
Next i
MsgBox "Done"
End Sub