Combine rows using condition Closed

Question

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

TrowaD · Answer

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

Combine rows using condition

1 response

Similar discussions

Subscribe To Our Newsletter!