Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine rows and Qty
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine rows and Qty
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine rows? | Excel Discussion (Misc queries) | |||
How do I combine worksheets w/o enough rows to combine? | Excel Worksheet Functions | |||
Combine rows and add semicolon | Excel Discussion (Misc queries) | |||
Combine rows | Excel Discussion (Misc queries) | |||
how to combine the multiple rows into one rows? | Excel Worksheet Functions |