Sometimes it's better to use what's built into excel.

You may want to look at Data|Subtotals or data|pivottable to get a nice summary.

George wrote:

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


--

Dave Peterson