Combine rows using condition

[Closed]
Report
Posts
1
Registration date
Monday December 2, 2013
Status
Member
Last seen
December 2, 2013
-
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
-
Hi,

I Have spreadsheet like this:

Col A Col B
02003SM TB4
02003SM TB5
02006SM TB1
07017SM C461
07025SM C462
07128SM C479
07139SM C3
07139SM C6
07139SM C13
07139SM C23

I want to count and combine Col B data using Col A Data as filter.
So output should be like this:

Col A Col B Col C
02003SM TB4,TB5 2
02006SM TB1
07017SM C461
07025SM C462
07128SM C479
07139SM C3,C6,C13,C23 4

Can anybody help me in achieving this.

Regards,
Pankaj

1 reply

Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
Hi Pankaj,

I put your sample data in column D and E. Make sure that duplicate entry's are adjacent to each other as they are now (use sort if they are not).

The following code will put result in columns A, B and C:
Sub RunMe()
Dim lRow As Integer
lRow = Range("D1").End(xlDown).Row
For Each cell In Range("D1:D" & lRow)
    If cell.Value = Range("A" & Rows.Count).End(xlUp).Value Then
        Range("B" & Rows.Count).End(xlUp) = Range("B" & Rows.Count).End(xlUp) & ", " & cell.Offset(0, 1)
        Range("C" & Rows.Count).End(xlUp) = Range("C" & Rows.Count).End(xlUp) + 1
    Else
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value
        Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, 1).Value
        Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = 1
    End If
Next cell
End Sub

Best regards,
Trowa