Sunday, July 14, 2013

Google Spreadsheet Formatting data in a Pivot Table

Question:

( by Stupac310 )

I have a list of items sold by various sales reps over a 24-month period. I have sales dates and in some cases cancellation dates. I would like to count the number of sales each particular rep had in each month. I would also like to count how many of those sales wound up as cancellations. (Browser: Chrome).

INPUT: Columns are (A) Item Sold; (B) Sales Rep; (C) Sales Date as xx/xx/xxxx; (D) Cancellation Date as xx/xx/xxxx. 

item sold        Jane     12/15/2012      06/10/2013
item sold        Fred     01/04/2013      03/25/2013
item sold        Jane     01/20/2013 

DESIRED OUTPUT: Columns would be (A) Sales Rep; (B, C, etc.) Month-Year; (Sub1) Sales ; (Sub2) Cancellations.

                Dec-2012           Jan-2013
            Sales     Cans     Sales    Cans
Fred        0            0           1           1
Jane        1            1           1           0

What formula do I put in these cells? Count/Filter formulas? Array formulas? Thanks for any insight.


Solution:

Have a look at the following screenshot of my Spreadsheet:



I have the following formula in Cell F1 (colored as yellow in the above screenshot):

=query(VMerge(arrayformula(if(C:C="";"";if({1,0,0,0};B:B;if({0,1,0,0};"Sales";if({0,0,1,0};1;text(C:C;"mm-yyyy"))))));arrayformula(if(D:D="";"";if({1,0,0,0};B:B;if({0,1,0,0};"Cans";if({0,0,1,0};1;text(D:D;"mm-yyyy")))))));"select Col1,sum(Col3) where Col1<>'Sales Rep' and Col1<>'' group by Col1 pivot Col4,Col2 ")

the format of the above formula's result is not the exact output as desired by you, but it is a single formula solution.


And if you want the exact desired output as per your requirement then have a look at this link:

http://igoogledrive.blogspot.com/2013/07/filtering-and-formatting-data.html

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

No comments:

Post a Comment