Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 have a result in Excel that consists of customer's invoices and payments. The data are on a separate row for every invoice and payment which looks like this:

enter image description here

I would like the invoice and payment to be combined in a single row and the outstanding indicating as 0 value. Below is my intended results (highlighted in bold):

enter image description here

You can download the spreadsheet here:

Can anyone help provide me with the required solution?

share|improve this question
1 is a bad way to share data. What rules do you want to use to combine rows where Date/Tran Type/Desc are different? – Rhys Gibson Jul 26 '12 at 5:25
Hi! Rhys, thank you for your reply. I hope that DESC can be sorted. Importantly is that I can combine the Original Value, Paid To Date and Outstanding into 1 row. – Chris Jul 26 '12 at 10:23
Rhys, if you need the Excel worksheet, please let me know how can I send it to you. Thanks – Chris Jul 26 '12 at 10:28

A pivot table is probably the easiest solution to your problem.

Set whichever identifiers you want into the row section, then use calculate the sum of the fields you want combined.

It will take a bit of playing around to get it to look exactly the way you want. Let me know in the. Comments if you run into specific problems from here.

share|improve this answer
Pynner, I have not used a Pivot table before and as such will need a lot of guidance from you. Chris – Chris Jul 26 '12 at 10:32
Pynner, I think there is a limitation of 256 data that I can drop in a column. I have a column which contains much more than 256. – Chris Jul 26 '12 at 10:42
Which value are you putting in the column? you should have two calculated values as columns if i understood your requirement correctly. – Pynner Jul 27 '12 at 2:33
Are you able to show me an example with my worksheet? My apology that I am not familiar with Pivot table. Thank you very much – Chris Jul 27 '12 at 7:19
There are a lot of resources for these things on youtube. Check out this search, there might be something helpful in the results: – simbabque Jul 27 '12 at 7:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.