Check rows for duplicate references and add related data

[Robert]

Excel 2002. I have a sheet containing 27K rows of data, each row
referenced by a reference number in column A. Many of the rows have
duplicate or triplicate references e.g.
01/010/0002
01/010/0014
01/012/0020
01/012/0020
01/012/0020
01/013/0134
01/014/0004
01/014/0004
but contain different values in subsequent columns.
What I need to do is identify the rows where the references are the same
and sum data in the corresponding rows e.g. for 01/012/0020 above I need
to sum the contents of column C for the 3 related rows and for
01/014/0004 sum the contents of column C for the 2 related rows. I know
how to identify duplicates in column A using =countif(A:A,A1) which will
give me 2 where there is a duplicate entry or 3 where there is a
triplicate entry, but I am not sure where to go from there. I imagine
that I need to extract the data to another range to sum the data in
column C for each reference.
Grateful for any assistance.

 

[Dave Peterson]

Take a look at Data|Subtotals (if your data is in sorted order).

Or even Data|Pivottable.

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Guest]

This will work if you can put the result in an adjacent column; you can
modify the approach if you need to:

At the end of your columns of data put a column that uses your COUNTIF to
count the multiples, but instead of counting the whole column, only count up
to the line you are on - thus for row 50 the formula would be:
=COUNTIF($A$1:$A50,$A50)
Reason: this will count duplicate entries ABOVE the current row, so it will
number each one in sequence (1 for the first row of a particular reference, 2
for the second, etc). Now you can identify the first one in each group and
on that line calculate your total using a SUMIF, so in the next column over
use this formula to sum the total for a particular reference (I will again
write it as for row 50, and assume the prior formula is in column D and I
want to sum Column C):
= IF($D50=1,SUMIF($A:$A,$A50,$C:$C),"")
So now once for each reference, on the first row for that particular
reference, the total of column C where A is equal to the reference value
should be calculated. You could even put in some sort of label to identify
it (in an adjacent column):
= IF($D50=1,"TOTAL FOR "&$A50&":","")
Hope this helps - at least to give some ideas you might be able to use.

 

[Robert]

Many thanks for the suggestions.

Robert

This will work if you can put the result in an adjacent column; you can
modify the approach if you need to:

At the end of your columns of data put a column that uses your COUNTIF to
count the multiples, but instead of counting the whole column, only count up
to the line you are on - thus for row 50 the formula would be:
=COUNTIF($A$1:$A50,$A50)
Reason: this will count duplicate entries ABOVE the current row, so it will
number each one in sequence (1 for the first row of a particular reference, 2
for the second, etc). Now you can identify the first one in each group and
on that line calculate your total using a SUMIF, so in the next column over
use this formula to sum the total for a particular reference (I will again
write it as for row 50, and assume the prior formula is in column D and I
want to sum Column C):
= IF($D50=1,SUMIF($A:$A,$A50,$C:$C),"")
So now once for each reference, on the first row for that particular
reference, the total of column C where A is equal to the reference value
should be calculated. You could even put in some sort of label to identify
it (in an adjacent column):
= IF($D50=1,"TOTAL FOR "&$A50&":","")
Hope this helps - at least to give some ideas you might be able to use.