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