Consolidate rows & amounts with the same heading

[Guest]

Hi,
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.
Thanks
George

 

[Otto Moehrbach]

I gather from what you say that this data was sorted by Column A? How many
Column A cells will have the same entry, just two? Or does that vary? HTH
Otto

 

[Guest]

Yes that is correct - it is sorted by column A

I gather from what you say that this data was sorted by Column A? How many
Column A cells will have the same entry, just two? Or does that vary? HTH
Otto

 

[Guest]

Otto - yes Column A cells will have at most 2 of the same entry

 

[Otto Moehrbach]

George
The following macro should do what you want.. As written, I assumed
that your data starts in row 2 with headers in row 1. The company names are
in Column A and the amounts are in Columns B & C. This macro will not work
for you if you have amounts in Columns D and beyond. I also assumed, as you
said, that the data is sorted by Column A. You also said that any one
company will have no more than 2 listings, so I wrote the code for that.
Note that you didn't say what you wanted done with the duplicate company
name row. I assumed that you wanted that row deleted.. Please post back if
you have any questions or want/need any changes. HTH Otto

Sub MergeData()
Dim RngColA As Range
Dim c As Long
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngColA.Count To 1 Step -1
If StrComp(RngColA(c), RngColA(c - 1)) = 0 Then
RngColA(c - 1).Offset(, 1).Value = _
RngColA(c - 1).Offset(, 1).Value + RngColA(c).Offset(,
1).Value
RngColA(c - 1).Offset(, 2).Value = _
RngColA(c - 1).Offset(, 2).Value + RngColA(c).Offset(,
2).Value
RngColA(c).EntireRow.Delete
End If
Next c
End Sub

 

[Guest]

Is there a reason why you cannot use Excel's consolidate feature for this?