# Combine rows using condition

Closed
pankaj.pandey Posts 1 Registration date Monday December 2, 2013 Status Member Last seen December 2, 2013 - Dec 2, 2013 at 11:59 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 5, 2013 at 10:46 AM
Hi,

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 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 5, 2013 at 10:46 AM
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