2

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:

http://www.fileserve.com/file/TVgW6vq/Samples.xls

Can anyone help provide me with the required solution?

3
  • 1
    fileserve.com is a bad way to share data. What rules do you want to use to combine rows where Date/Tran Type/Desc are different? Jul 26, 2012 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, 2012 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, 2012 at 10:28

1 Answer 1

1

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.

5
  • 1
    Pynner, I have not used a Pivot table before and as such will need a lot of guidance from you. Chris
    – Chris
    Jul 26, 2012 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, 2012 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, 2012 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, 2012 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: youtube.com/results?search_query=excel+pivot+table
    – simbabque
    Jul 27, 2012 at 7:27

You must log in to answer this question.

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