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
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