Prev: keyboard with excel
Next: Auto-Close
From: jml2008 on 12 Apr 2010 13:23
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!
From: Dave Peterson on 12 Apr 2010 14:24
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).



jml2008 wrote:
>
> 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
 | 
Pages: 1
Prev: keyboard with excel
Next: Auto-Close