Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to sum up all the values with the same invoice number to the first row that contains that invoice number. After which, deleting the rows that have the same invoice number that are below the first.

Can anyone help me with obtaining this requirement using a macro?

share|improve this question
what have you tried so far? have you writtent a few lines of code? recorded something? – tumchaaditya Aug 1 '12 at 11:32
tumchaaditya, I have not done anything as I have no clue as to how should I proceed. – Chris Aug 2 '12 at 13:16
How familiar are you with VBA? Have you tried using the macro recorder, then generalizing the problem? I would try the following (with the macro recorder): use a formula in a 3rd column to calculate the sum for each invoice #, copy the results and paste them back in the 3rd column as values only, then select the columns and use the Remove Duplicates option (under the Data tab) and choose to remove the duplicates in the 1st column (uncheck the others). You'll be left with unique invoice #s and their sums. – Zairja Aug 2 '12 at 19:17

A no-VBA solution (my favorite kind) would be to simply convert your data into a Table (Insert>Table). Then create a Pivot Table (Insert>PivotTable) using your data Table as its source. This allows you to keep your source data without modification, and your Pivot Table can essentially subtotal your amounts by invoice number. Here's an example based on your sample data:

Subtotal Invoice

share|improve this answer
David, thank you for your suggestion but I am not able to use the pivot table just yet as I need to perform other tasks after having done the above requirements. – Chris Aug 2 '12 at 13:17
What are your other tasks? Can you share some additional sample data and desired result? – F106dart Aug 3 '12 at 14:54
Thank you for all your suggestions. I will use the Pivot table method as suggested by David. Should I need further details, I will consult you guys again. – Chris Aug 6 '12 at 1:14
@Chris if you need help with the next tasks you mentioned, let us know, there's probably a creative solution to help. – dav Aug 6 '12 at 13:35
@Chris, if this answer helped you, don't forget to mark it – SeanC Aug 8 '12 at 18:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .