Consolidate rows & amounts with the same heading

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

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
"George" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Yes that is correct - it is sorted by column A

"Otto Moehrbach" wrote:

> 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
> "George" <[email protected]> wrote in message
> news:[email protected]...
> > 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 - yes Column A cells will have at most 2 of the same entry

"George" wrote:

> Yes that is correct - it is sorted by column A
>
> "Otto Moehrbach" wrote:
>
> > 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
> > "George" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
> >

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
"George" <[email protected]> wrote in message
news:[email protected]...
> Otto - yes Column A cells will have at most 2 of the same entry
>
> "George" wrote:
>
>> Yes that is correct - it is sorted by column A
>>
>> "Otto Moehrbach" wrote:
>>
>> > 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
>> > "George" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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
>> > >
>> > >
>> >
>> >
>> >

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


"George" wrote:

> 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
>
>