I still like the macro idea, but you could insert two new columns (F & G) and
put this in F1 and drag down:
=IF(COUNTIF($A$1:A1,A1)1,"DELETETHISLINE",SUMIF($ A$1:$A$100,A1,$B$1:$B$100))
And in G1 (and drag down):
=IF(COUNTIF($A$1:A1,A1)1,"DELETETHISLINE",SUMIF($ A$1:$A$100,A1,$C$1:$C$100))
Change those ranges to match your data (I stopped at row 100).
Then select columns F & G and
edit|copy
edit|paste special|Values
Apply data|filter|Autofilter
to column F.
show just the "deletethisline" rows.
select those visible cells and delete them.
Then copy F over column B and G over column C.
And delete F and G.
Donna YaWanna wrote:
Whoa! I don't even understand the answer. It's not your fault, I'm just a
techno clutz. Is it possible to do this same thing with a formula? Those I
can copy and manipulate.
"Dave Peterson" wrote in message
...
You could use a little macro:
Option Explicit
Sub testme()
Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
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
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value + .Cells(iRow,
"B").Value
.Cells(iRow - 1, "C").Value _
= .Cells(iRow - 1, "C").Value + .Cells(iRow,
"C").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
This destroys the data, so test it against a copy of your worksheet.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Donna YaWanna wrote:
I have files that contain a header row and then thousands of rows of
data.
Column A is a customer identifyer label that is unique. There's only 1
row
per customer.
EXCEPT for my problem. In some cases, we've repeated the rows to split
off
have of the data in the other cells to be assigned to 1 salesman amd the
other half to another salesman.
Now I need to merge them all back together so that...
X12345 100 2000 Smith Jones
X12345 100 2000 Smith Miller
Y12345 300 3000 Johnson Howard
becomes....
X12345 200 4000 Smith Miller
Y12345 300 3000 Johnson Howard
So the amounts are added and the name that appears first is the one that
is
used.
Is there an easy way to do this?
Thanks
--
Dave Peterson
--
Dave Peterson