Find duplicates & sum values in range

**potat5656** · 09-20-2010, 09:18 AM

Hello Excel Forum members:

workbook has four columns:
A B C D
Owner Amount Unique # Update

Column C contains ID numbers that are unique for their update date, owner and amount. For instances where there is more than unique number in a row I would like to sum the amounts for all rows containing that unique #, delete the remaining rows, and use the most recent update date.

I.e.

Data was:
owner Amount Unique # Update
x 131 60505617 6/30/2010
x 1193 60505682 3/31/2010
x 382 165167842 3/31/2010
x 832 283678209 3/31/2010
x 70 60505682 5/31/2010
x 10585 313400624 6/30/2010
x 1040 313400640 6/30/2010
x 2154 313400681 6/30/2010
x 4569 313400673 6/30/2010
x 579 313400681 6/30/2010

AFTER VBA SCRIPT MACRO: DATA SHOULD BE

owner Amount Unique # Update
x 131 60505617 6/30/2010
x 1263 60505682 5/31/2010
x 382 165167842 3/31/2010
x 832 283678209 3/31/2010
x 10585 313400624 6/30/2010
x 1040 313400640 6/30/2010
x 2733 313400681 6/30/2010
x 4569 313400673 6/30/2010

So unique ID number 60505682 & 313400681 were consolodated-the amount column values were summed, the most recent update date was choosen.

Can someone please help me create macro to eliminiate adn consolodate duplicate entries?

Thanks for all your help excel forum!

-Christian Kendall

Entire Data set. Can't post excel sheet to forum for some reason...

EXCEL DATA:

owner Amount Unique # Update
XANADU 131 60505617 6/30/2010
XANADU 1193 60505682 3/31/2010
XANADU 382 165167842 3/31/2010
XANADU 832 283678209 3/31/2010
XANADU 70 306122201 5/31/2010
XANADU 10585 313400624 6/30/2010
XANADU 1040 313400640 6/30/2010
XANADU 2154 313400665 6/30/2010
XANADU 4569 313400673 6/30/2010
XANADU 579 313400681 6/30/2010
XANADU 513 313400699 6/30/2010
XANADU 325 313400731 6/30/2010
XANADU 258 313400749 6/30/2010
XANADU 219 313400772 6/30/2010
XANADU 747 313400830 6/30/2010
XANADU 201 313400863 6/30/2010
XANADU 706 313586737 5/31/2010
XANADU 19157 313586752 6/30/2010
XANADU 490 313586778 6/30/2010
XANADU 515 313586836 6/30/2010
XANADU 365 313586844 6/30/2010
XANADU 281 313586877 6/30/2010
XANADU 149 313586885 6/30/2010
XANADU 10913 345395206 3/31/2010
XANADU 49 442487112 5/31/2010
XANADU 24 524908720 12/31/2009
XANADU 298 743410409 6/30/2010
XANADU 75 842400509 5/31/2010
XANADU 11 906548508 5/31/2010
XANADU 465 949746804 3/31/2010
XANADU 2114 7.80E+12 10/31/2009
XANADU 1052 2.22E+210 6/30/2010
XANADU 700 00075WAP4 5/31/2010
XANADU 77 000780KM7 10/31/2009
XANADU 1823 00081TAB4 10/31/2009
XANADU 7108 00081TAB4 6/30/2010
XANADU 620 00081TAC2 10/31/2009
XANADU 910 00081TAC2 4/30/2010
XANADU 276 000886AB7 10/31/2009
XANADU 22468 00103XAC7 10/31/2009
XANADU 22032 00103XAC7 5/31/2010
XANADU 2744 00103YAE1 10/31/2009
XANADU 2642 00103YAE1 5/31/2010
XANADU 26 00103YAF8 10/31/2009
XANADU 3000 00104CAA6 10/31/2009
XANADU 3000 00104CAA6 5/31/2010
XANADU 1350 001055AC6 10/31/2009
XANADU 1575 001055AD4 5/31/2010
XANADU 700 001192AC7 10/31/2009
XANADU 439 001192AF0 10/31/2009
XANADU 2224 00130HBA2 10/31/2009
XANADU 2705 00130HBA2 6/30/2010
XANADU 12350 00130HBC8 10/31/2009
XANADU 12865 00130HBC8 5/31/2010
XANADU 825 00130HBH7 10/31/2009
XANADU 11170 00130HBH7 5/31/2010
XANADU 1510 00130HBL8 10/31/2009
XANADU 7505 00130HBL8 6/30/

**zbor** · 09-20-2010, 09:20 AM

Hi potat,

here is one tip if you want to get your question answered quickly (form the forum rules):

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

**potat5656** · 09-20-2010, 12:25 PM

ATTACHED IS THE WORKBOOK:

I can only upload the file as a .zip file even though the original file size is less than 1,000kb. Sorry for the inconvenience!