Pivot Table Help

[ianamck]

Hi All

I regularly have to produce reports that initially only had a few records each week and so was just simple and easy to manipulate the raw data rather than have a more automated solution.

Now I am getting more and more records which means I need a better solution.

You can find a sample of the data I have here

https://www.dropbox.com/s/rrhfhecu4a42hit/exportdata.csv

What I am simply trying to do without much success is to calculate the total sale per customer. When the customer only orders one item then it creates one line of data which has the information I want. However when they order multiple items it records a line for each item with a total for the item purchase, but also shows the total order value duplicated on each and every line of there order as well as the total order shipping cost duplicated on each line.

So when I try to use a pivot table to calculate the total order value per order no it calculates the total wrong. Can someone help me try an resolve this so the pivot table will show individual order no with all products ordered, sub total for each product and a total for shipping once and the total order value once.

Regards

Ian McK

 

[NARAYANK991]

Hi Ian ,

Is it OK if a helper column is introduced in the raw data , so that the TOTAL column is not used in the pivot table ?

Can you check the file I have uploaded ?

Narayan

 

[ianamck]

Do you know Narayank991 I didn't even think about a helper column, you would think I would remember when I see it used in lots of example on here. What a dumb &^% I am. Although saying that I am not sure I am still good enough to have come up with that formula. Still struggle with nested formulas. Will try working with that now and see how I get on, will report back shortly.

You are my saviour and many thanks