Sum and delete Duplicate Rows VBA MACRO FORMULA

[ARNO]

Hello. I am in desparate need of a formula or VBA or MACRO for my problem. I have a about 5 spreadsheets of 3,000+ entries. All only have 2 columns, A & B. Column A has the model numbers and Column B has the quantity. There are many duplicates of different entries in Column A and I need to add up their quantities and make it into 1 row.
Example:
A B
Model Number Quantity
1234 2
4321 1
1234 2
4321 1


A B
Model Number Quantity
1234 4
4321 2


I cannot use a pivot table. Please help with this! Thank you!!

[StephenR]

Why can't you use a pivot table which would appear to be perfectly suited? In any case here is one VBA approach:

Please Login or Register  to view this content.

[Leith Ross]

Hello ARNO,

When this macro is run it will create a summary report. The data is listed alphabetically form A to Z by model number. Leading and trailing spaces and case are ignored when gathering the data. If the sheet doesn't already exist the macro will create it.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[ARNO]

Leith Ross's approach did not work. It stated:

Runtime error '91.'
Object Variable or With Bock Variable not set.


When I debugged it, it highlighted the following line:

Please Login or Register  to view this content.

StephenR's approach also did not work, it stated the following:

Runtime error '1004'
The extract range has a missing or illegal field name.

debugged, highlighted line:

Please Login or Register  to view this content.

How can I fix this? I need to delete the duplicate model numbers but sum their quantities in one row for each. Thanks for your responses!

[Leith Ross]

Hello ARNO,

The macro I wrote works in Excel 2003. Which version of Excel are you using?

[Leith Ross]

Hello ARNO,

Sorry, it is my fault. I changed the macro but didn't post the corrected copy. The correction is in blue.

Please Login or Register  to view this content.

[ARNO]

I am using excel 2007. The macro didnt function. It states:

Compile error: syntax error.

That's all it said, no debug. Thank you for helping me. Please let me know how to sum up duplicate model number's quantities. I am only using column A and B. I would like the summed quantity to stay in column B with the model numbers in column A. All the duplicates I wish to be deleted but their quantities summed. Thank you so much!

[MAN]

I found this post useful (stephenR's approach in particular) and would like to know how to modify it if the rows had 2 other items instead of just 1. And if possible the totals for each model in a new column E.



for example:

A B C D
Model type1 type2 type3
1234 2 1 4
4321 1 4 3
1234 2 3 1
4321 1 1 4


A B C D E
Model type1 type2 type3 total
1234 4 4 5 13
4321 2 5 7 14

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then start your own thread.

[vijay_g]

Hi Ross

Can you advise same for sheet , where summary has to be created for one sheet only, with 10 columns, say A,B,C,D,E,F,G,H,I,J, and duplicay is value is in "E" and amount is in "G"

thanks in advance

[shg]

Same goes for you, vijay.