If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Compare and consolidating duplicated rows
Hi Otto,
Can you please re-post the macro you mentioned for George, or somethjing similar? I need to consolidate duplicate text cells and add up their combined quanties. (i.e.) Before A B DESCRIPTION QTY BAC3020 | BOLT 12 CBS2059 | WASHER 6 BAC3020 | BOLT 8 RIV1520 | RIVET 2 WAS6875 | WASHER 7 CBS2059 | WASHER 13 RIV1520 | RIVET 8 After A B DESCRIPTION QTY BAC3020 | BOLT 20 CBS2059 | WASHER 19 RIV1520 | RIVET 10 WAS6875 | WASHER 7 Thanks, Tony S. "Otto Moehrbach" wrote: George I gave you a macro for that in your 12 Mar 06 posting. Otto "George" wrote in message ... Hi Dave, My question is a little similar to Jen's: Can you please help with this? I am trying to write a macro whereby it searches for the same text as the cell below it and makes one singular cell but adds the units & market values in the cells next to them. E.g - before macro Stock, units, mkt val (these are the headings) ABC (cell A1) 300 (cell B1) 1500 (cellC1) ABC (cell A2) 400 (cell B2) 2000 (cellC2) after macro ABC (cell A1) 700 (cell B1) 3500 (cellC1) This needs to be performed for many different stocks over the worksheet. S/s is sorted by col A. Thanks George "Dave Peterson" wrote: Try this against a copy of your worksheet (it destroys the original data). Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim iCol As Long Dim FirstRow As Long Dim LastRow As Long Dim maxColsToCheck As Long maxColsToCheck = 50 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 'do nothing Else For iCol = 2 To maxColsToCheck If UCase(.Cells(iRow - 1, iCol).Value) = "O" Then .Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value End If Next iCol 'delete that duplicate .Rows(iRow).Delete End If Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jen wrote: I have a worksheet that has over 4000 rows and 50 columns of information. An individual may be listed multiple times. I am looking to compare the information and if duplicated consolidate the duplicated data to one row. Here is an example of the worksheet: colA colB colC colD colE colF colG JSMITH O M DIST V O 1 JSMITH 1 M O O DIST 1 End result I would like to see: colA colB colC colD colE colF colG JSMITH 1 M DIST V DIST 1 If, JSMITH, is listed twice, have formula look at JSMITH's information for each column and pull to one row, if "O" pull the other value if different. -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|