• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract customer data from sales data pivot table?

U

ultimathor

New Member
The title may sound a little odd and I am not sure what the right wording is. However, what I would like to do should be pretty straightforward - I just don't know how to do it using pivot table.


I have sales data with a number of standard columns including


transaction id

client name

client country

client email

product category

... etc


What I need to do is create a list with the following columns for each product category


client name

client email

client country


Most clients will only buy products from one category but some buy from several. Each transaction has one product and each product belongs to one product category.


I have been trying to have my pivot table with client email and client country as row labels, product category as column labels and count of transaction id as sum value but I get a sum row for each email that I would really like to get rid of.


I also tried using product category as report filter but had the same problem.


I guess that what I'm trying to do is not what Pivot Tables are designed for but somehow I think it should be possible. It could be done by making one table that had just client email as row label and product category as column label and count of transaction id as sum value. Then I could use a lookup to get client country. While that would work, it is unsatisfying and I have a feeling it should be possible to do in a more intelligent manner.


I am working on Excel 2011 for Mac, which is a bit frustrating most of the time and very frustrating the rest of the time... If necessary, I can run Excel 2013 under Parallels but Excel works very slowly like that so I'd prefer not to.


I hope someone has a good idea as to how to proceed.


Thanks,


Thor
 
Hello Thor,

I am not sure I am understanding what you are after. Could you post some sample data (say 10 rows) that contain the issues you are describing? Also, please post your desired output for that sample data.


Cheers,

Sajan.
 
Hi Sajan,

I admit it is not easy to understand when reading my post. Here is a list of sales data with relevant data:

[pre]
Code: 
Transaction ID	Artist          Instrument	Amount	Country	Email
218726      Reinhold Friedrich      Trumpet	24,2919     US	benjaminpaille@hotmail.com
163243      Lee Morgan              Clarinet	2,282267    DK	ebbes@mail.tele.dk
163581      Lee Morgan              Clarinet	2,282267    DK	ebbes@mail.tele.dk
218658      Radovan Vlatkovic       Horn	15,89205    CA	fiona.chisholm4@gmail.com
218693      Lee Morgan              Clarinet	40,376923   NO	fred-gul@online.no
218694      Charles Neidich         Bassoon	24,222923   NO	fred-gul@online.no
218695      Yehuda Gilad            Clarinet	24,222923   NO	fred-gul@online.no
218696      Yehuda Gilad            Clarinet	16,145923   NO	fred-gul@online.no
218708      Emmanuel Pahud          Flute	32,091975   IT	giorgioconsolati@yahoo.it
218672      Emmanuel Pahud          Flute	32,091975   IT	giuliofrancesconi@gmail.com
218660      Reinhold Friedrich      Trumpet	16,27186    US	herseth2001@hotmail.com
218661      Reinhold Friedrich      Trumpet	24,41186    US	herseth2001@hotmail.com
218752      Kristian Steenstrup     Trumpet	16,241875   US	herseth2001@hotmail.com
218753      Reinhold Friedrich      Trumpet	16,241875   US	herseth2001@hotmail.com
218665      Emmanuel Pahud          Flute	15,89205    DE	kim.dongjin@yahoo.co.kr
218681      Emmanuel Pahud          Flute	32,239938   HK	loosze_wang@hotmail.com
218682      Emmanuel Pahud          Flute	16,115938   HK	loosze_wang@hotmail.com
218683      Emmanuel Pahud          Flute	16,115938   HK	loosze_wang@hotmail.com
[/pre]
What I am looking for is a way to extract customer emails from the data and for each email get the country the client is from and instrument (product type).


Email Country

benjaminpaille@hotmail.com US

ebbes@mail.tele.dk DK

fiona.chisholm4@gmail.com CA

fred-gul@online.no NO

giorgioconsolati@yahoo.it IT

giuliofrancesconi@gmail.com IT

herseth2001@hotmail.com US

kim.dongjin@yahoo.co.kr DE

loosze_wang@hotmail.com HK

`

The goal of the exercise is to make an email list for each instrument that can be used for newsletters to customers who have bought a product with a given instrument. The list should include the email address, the name (I didn't include that in the sample data) and the country and potentially other information.


I hope this makes it a little more clear :-).


Thanks for you help,


Thor
 
Good day Thor


I have uploaded a sample pivot for you to "play around" with, I hope it is of help


https://dl.dropboxusercontent.com/u/75495784/ultimathor.xlsx
 
Hi bobhc,


Thanks for the pivot table. It took me to the same place I came on my own, but not quite all the way :-).


What I'm trying to accomplish is to have a column in the pivot table that lists the country for each email address. I can get half the way by removing Instrument from row labels and inserting Country instead and chosing Outline layout or Tabular layout. However, when I do this, I get two rows for each email - one with the email and one with the country. What I'm trying to get is both email and country in the same row.


Any suggestions? Thanks :-).


Thor
 
Hi ultimathor,


I might be wrong, or i could not understood your problem clearly, but i think you are complicating issue. Why not just sort the table for Instruments and you will have a sorted list for email instrument and country wise??


Faseeh
 
Hello Thor,

Is the following similar to what you are trying to get?

[pre]
Code: 
Instrument	Email	                         Country	Count of Tran ID
Bassoon	        fred-gul@online.no	          NO		1
Clarinet	ebbes@mail.tele.dk	          DK		2
Clarinet	fred-gul@online.no	          NO		3
Flute	        giorgioconsolati@yahoo.it	  IT		1
Flute	        giuliofrancesconi@gmail.com	  IT		1
Flute	        kim.dongjin@yahoo.co.kr	          DE		1
Flute	        loosze_wang@hotmail.com	          HK		3
Horn	        fiona.chisholm4@gmail.com	  CA		1
Trumpet	        benjaminpaille@hotmail.com	  US		1
Trumpet	        herseth2001@hotmail.com	          US		4
Grand Total	                         			18
[/pre]
This was created by showing items in tabular form, and repeating the labels.


If this is not what you are after, could you post the sample output you are getting with the e-mails in two rows?


Cheers,

Sajan.
 
Hello Sajan,


Yes, that is exactly what I'm trying to get. What do you mean by "repeating the labels" and where do you select it?


Thanks,


Thor
 
@ Faseeh,


Thanks for your reply. It would partly solve the problem, but as the customers can have many transactions per instrument, the list would have their email addresses one time fore each transaction, if I understand your suggestion correctly.


Thor
 
Hello Thor,

There is a checkbox on the Field Settings screen | Layout & Print tab called "Repeat Item Labels". Selecting it will repeat the labels for that field, instead of grouping it.


In the above sample output, I selected it for the "Instrument" field.


Hope that helps.


Cheers,

Sajan.
 
Hello Sajan,


Perfect. For some reason, that option is not there in the Excel for Mac version (2011). But if I make the pivot table in Excel 2013 and save it and then open it in Excel 2011 for Mac it still works.


Thanks once again for your help :-).


Thor
 
You must log in or register to reply here.
Back
Top