CODEWITHSUNDEEP
excelvba
user2822837
user2822837

Reputation: 1

Consolidate Duplicate Rows - Excel VBA?

I am able to merge cells with the same SKU and delete the row with the code below, however I don't know how to also combine sku + qty in the same row.

id     sku   Qty Total Qty  Weight  Address Zip     etc...
576996  A     1      7       2.6            
576996  B     1      7       2.6            
576996  C     2      7       2.6            
576996  D     3      7       2.6            

Current code sku              Qty               
576996  A, B, C, D     1, 1, 2, 3     7  2.6



Desired                            Qty          
576996  A (1),B(1),C(2),D(3)    1, 1, 2, 3     7    2.6 


Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 3 Step -1
    With sh
        If .Cells(i, 1) = .Cells(i - 1, 1) Then
            If .Cells(i - 1, 2).Value <> .Cells(i, 2).Value Then
                .Cells(i - 1, 2) = .Cells(i - 1, 2).Value & ", " & .Cells(i, 2).Value
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ", " & .Cells(i, 3).Value
            End If
            Rows(i).Delete
        End If
    End With
Next
End Sub

Upvotes: 0

Views: 233

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

Just extend the line that sets sku

 .Cells(i - 1, 2) = .Cells(i - 1, 2).Value & ", " & _ 
     .Cells(i, 2).Value & " (" & .Cells(i, 3) & ")"

Upvotes: 1

Related Questions