Combine rows and Qty

[jml2008]

Please Help!!!!
My need is to sort, combine similar values of rows in column 1 (having 1
value in cell at the end), then combine refdes in column 2, separated by a
comma, (ex C4,C8), then adding quanities (scroll down to see desired end
result)

PartType RefDes PartDecal QTY
CAP_330PF_0805 C3 805 1
CAP_470PF_0805 C4 805 1
CAP_470PF_0805 C8 805 1
CAP_470PF_0805 C9 805 1
CAP_330PF_0805 C11 805 1

PartType RefDes PartDecal QTY
CAP_330PF_0805 C3,C11 805 2
CAP_470PF_0805 C4,C8,C9 805 3


Thanks sooo much for your help!

 

[Dave Peterson]

It really looks like you're combining those cells based on both column A and C.
If that's true...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
If .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value Then
'match in both A and C
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B") & ", " & .Cells(iRow, "B").Value
.Cells(iRow - 1, "D").Value _
= .Cells(iRow - 1, "D").Value + .Cells(iRow, "D").Value
.Rows(iRow).Delete
End If
End If
Next iRow
End With
End Sub

Save your work before you test--or test against a copy of the worksheet. This
procedure deletes rows while running.

Also, it assumes that your data is already sorted nicely (by column A and C).