Excel: Pivot Table with Unique values only

Hello Team,

As per the attachment, I am trying to use Pivot tables/charts to arrive at the following:

Show the total sales "Amount" for each client "Name" where the "Num" is unique. i.e. Each "Num" or invoice contains a number of line items and I only want the total of each, and lastly the count of unique invoices "Num" elements.

Can you assist ?
Peter.

Dear Frnd,

There are certain rules to insert a pivot table

Most imp is - on top row there should not be any black heading. I found it on your data and deleted the. Hope it has not changed your data.
I hope u must be getting error due to it.

Rest is selection of data in row and value of pivot table field.

attached is the updated sheet, hope you will find the required data.

Regards,

Kamal Asharaf.

Hi Peter,

As you may have seen from the alert when trying to put your data into a Pivot Table that "you must use data that is organized as a list with labeled columns".
Your data has a lot of blank columns. I see this a lot when data is exported from some other database system.

Sometimes the process of correcting such bad formatting can be very cumbersome.

You can use a macro such as the one on this site to remove the blank columns.

http://www.excelhowto.com/macros/del...use-excel-vba/

Once the blanks are removed you can select the area you want and then insert a PivotTable.

As for getting the results you want in a Pivot Table, I recommend you experiment with it to find the results you want.

Hope this is helpful.

Cheers