Use Macro to Sum Dupes then delete - Help please

[annasproule]

I am trying to use a macro to sum all duplicates, then delete all but one. I have data in column A that is duplicated, totals in column B that I would like summed. I can add al into B or sum in C, I would then like all of the dupes to be deleted (except one with the sum, of course). I tried Pivot Tables and couldn't make them work.


Column A Column B Column C
Invoice Gross Amt Gross amt no Dupes
1234 100.00
1234 -50.00
1245 200.00
1245 -50.00
1234 25.00

[MarvinP]

Hi annasproule and welcome to the forum,

I'd try pivot tables again. It will sum and only show a single Invoice Number. Leave the dupes. See the attached.

Also read http://office.microsoft.com/en-us/ex...001034626.aspx for dupes in 2003 but it got a lot easier with 2007. http://www.lytebyte.com/2008/06/10/h...es-excel-2007/

[annasproule]

The total number of columns and rows exceed what it will let me pull in for the pivot table. I have over 20K rows and 25 columns. I am not really very good at this stuff, but my programmer is out for 2 weeks, so I am trying to relearn from like 10 years ago

[MarvinP]

Hi Annasproule,

You said "exceeds what it well let me pull into a pivot table". That surprised me as doing a pivot table is a lot easier than writing VBA code. Read http://support.microsoft.com/kb/820742 and this was for 2000 excel.

Do you get an error message? Can we help with the "exceeds" problem?

If you could do a single pivot table and then copy and paste it to "Values Only" you would get your answer without needing any code.

If Pivot Tables won't do it for you, how about an Advanced Filter to get unique Invoice numbers. Then do a SumIf formula to add all amounts tied to that invoice number. Might this work for you? See http://www.contextures.com/xladvfilter01.html for this topic.

[Leith Ross]

Hello annasproule,

Since you said you have 25 columns, I am guessing there is one column for the invoice number and 24 more for each month spanning 2 years. If your worksheet layout is the same as the layout of the attached example then you can copy the macro into your workbook. You made need to change the worksheet names in macro. These are in marked in bold below.

The Grand totals are listed on a separate sheet. This allows you have a backup of the original data.

Please Login or Register  to view this content.

[annasproule]

Marvin,
It give me an error message when I pull over all of the Columns. I need the data from all of the columns. The error message says that the number of columns exceed 250

[annasproule]

Leith,

The columns have other pertinent data. I have attached a more lengthy example below. The data that has duplicates is in column A. The data I need summed is in column Q. I cannot get anything to work I have additional columns after Q also, but they are confidential, so I cannot post.

[Leith Ross]

Hello annasproule,

Easy fix. The correction is in bold below. The attached workbook has the macro and a button to run it added on "Sheet2".

Please Login or Register  to view this content.

[MarvinP]

Hi,

I'm guessing it was with earlier versions of Excel that this limit was reached.
Is there a chance you could limit the columns of the Pivot table to include less? If you made your column range from columns A to Q as in your sample it might work fine. See the attached to see.

[watersev]

see attachment, save the attached file and make sure the following reference is on:

1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. Run macro "Test"

[annasproule]

Leith,

Thank you SOOOOOO much!! That worked perfectly!!

I now have a different predicament. Maybe I should have posted a longer question. LOL

So now I have all of the data summed, but it left out all of the other info. I will post a new thread since it is a somewhat different issue.
Thank you very much for your patience!!