software

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 amp; 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
quot;Georgequot; gt; wrote in message
...
gt; Hi,
gt; I am trying to write a macro whereby it searches for the same text as the
gt; cell below it and makes one singular cell but adds the units amp; market
gt; values
gt; in the cells next to them.
gt; E.g - before macro
gt; Stock, units, mkt val (these are the headings)
gt; ABC (cell A1) 300 (cell B1) 1500 (cellC1)
gt; ABC (cell A2) 400 (cell B2) 2000 (cellC2)
gt; after macro
gt; ABC (cell A1) 700 (cell B1) 3500 (cellC1)
gt; This needs to be performed for many different stocks over the worksheet.
gt; Thanks
gt; George
gt;
gt;
Yes that is correct - it is sorted by column A

quot;Otto Moehrbachquot; wrote:

gt; I gather from what you say that this data was sorted by Column A? How many
gt; Column A cells will have the same entry, just two? Or does that vary? HTH
gt; Otto
gt; quot;Georgequot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt; I am trying to write a macro whereby it searches for the same text as the
gt; gt; cell below it and makes one singular cell but adds the units amp; market
gt; gt; values
gt; gt; in the cells next to them.
gt; gt; E.g - before macro
gt; gt; Stock, units, mkt val (these are the headings)
gt; gt; ABC (cell A1) 300 (cell B1) 1500 (cellC1)
gt; gt; ABC (cell A2) 400 (cell B2) 2000 (cellC2)
gt; gt; after macro
gt; gt; ABC (cell A1) 700 (cell B1) 3500 (cellC1)
gt; gt; This needs to be performed for many different stocks over the worksheet.
gt; gt; Thanks
gt; gt; George
gt; gt;
gt; gt;
gt;
gt;
gt;

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

quot;Georgequot; wrote:

gt; Yes that is correct - it is sorted by column A
gt;
gt; quot;Otto Moehrbachquot; wrote:
gt;
gt; gt; I gather from what you say that this data was sorted by Column A? How many
gt; gt; Column A cells will have the same entry, just two? Or does that vary? HTH
gt; gt; Otto
gt; gt; quot;Georgequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi,
gt; gt; gt; I am trying to write a macro whereby it searches for the same text as the
gt; gt; gt; cell below it and makes one singular cell but adds the units amp; market
gt; gt; gt; values
gt; gt; gt; in the cells next to them.
gt; gt; gt; E.g - before macro
gt; gt; gt; Stock, units, mkt val (these are the headings)
gt; gt; gt; ABC (cell A1) 300 (cell B1) 1500 (cellC1)
gt; gt; gt; ABC (cell A2) 400 (cell B2) 2000 (cellC2)
gt; gt; gt; after macro
gt; gt; gt; ABC (cell A1) 700 (cell B1) 3500 (cellC1)
gt; gt; gt; This needs to be performed for many different stocks over the worksheet.
gt; gt; gt; Thanks
gt; gt; gt; George
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

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 amp; 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(quot;A2quot;, Range(quot;Aquot; amp; 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
quot;Georgequot; gt; wrote in message
...
gt; Otto - yes Column A cells will have at most 2 of the same entry
gt;
gt; quot;Georgequot; wrote:
gt;
gt;gt; Yes that is correct - it is sorted by column A
gt;gt;
gt;gt; quot;Otto Moehrbachquot; wrote:
gt;gt;
gt;gt; gt; I gather from what you say that this data was sorted by Column A? How
gt;gt; gt; many
gt;gt; gt; Column A cells will have the same entry, just two? Or does that vary?
gt;gt; gt; HTH
gt;gt; gt; Otto
gt;gt; gt; quot;Georgequot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt; Hi,
gt;gt; gt; gt; I am trying to write a macro whereby it searches for the same text as
gt;gt; gt; gt; the
gt;gt; gt; gt; cell below it and makes one singular cell but adds the units amp; market
gt;gt; gt; gt; values
gt;gt; gt; gt; in the cells next to them.
gt;gt; gt; gt; E.g - before macro
gt;gt; gt; gt; Stock, units, mkt val (these are the headings)
gt;gt; gt; gt; ABC (cell A1) 300 (cell B1) 1500 (cellC1)
gt;gt; gt; gt; ABC (cell A2) 400 (cell B2) 2000 (cellC2)
gt;gt; gt; gt; after macro
gt;gt; gt; gt; ABC (cell A1) 700 (cell B1) 3500 (cellC1)
gt;gt; gt; gt; This needs to be performed for many different stocks over the
gt;gt; gt; gt; worksheet.
gt;gt; gt; gt; Thanks
gt;gt; gt; gt; George
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
Is there a reason why you cannot use Excel's consolidate feature for this?
--
Thanks,
MarkNquot;Georgequot; wrote:

gt; Hi,
gt; I am trying to write a macro whereby it searches for the same text as the
gt; cell below it and makes one singular cell but adds the units amp; market values
gt; in the cells next to them.
gt; E.g - before macro
gt; Stock, units, mkt val (these are the headings)
gt; ABC (cell A1) 300 (cell B1) 1500 (cellC1)
gt; ABC (cell A2) 400 (cell B2) 2000 (cellC2)
gt; after macro
gt; ABC (cell A1) 700 (cell B1) 3500 (cellC1)
gt; This needs to be performed for many different stocks over the worksheet.
gt; Thanks
gt; George
gt;
gt;