Can I create pivot table from subset of data?

November 20, 2011 9:56 PM

Can I create pivot table from subset of data? If not, is there another way to do what I want to do?

I'm sorry if this is a dumb question --- I've used pivot tables a bit in excel, and can usually grope my way toward a solution, but this has me stumped. I've tried googling and the Mr. Excel forums, but I'm afraid most of what I've come up with has been a bit over my head.

I'm trying to answer some questions using a large data set of transactions (it had to be broken up in order to avoid exceeding the row limit for Excel). I use excel 2007 at work and excel 2003 for Mac at home.

Basically, what I would like to do is to examine a subset of this data. For the sake of example, let's say it's customers who have bought items on different dates. What I'd ultimately like to do is pull all these "same customer" transactions out and compare the difference in the amount purchased on the different dates, so that one could generate stats like, "on second visit the amount purchased declined by an average of X%"

The trouble I'm having is that the customer data is in different fields, so that it's like A34=John B34=L. C34=Sullivan. I can obviously do a pivot table to group and count instances of a certain name, but I'm stuck on what the next step would be to be able to look at the other data associated with that name.

I feel like I probably need some sort of IF formula, but I'm not sure how to apply that in this instance, and how those work with pivot tables. Does anyone have any suggestions?

posted by Diablevert to Computers & Internet (6 answers total)

Best answer: Presumably there is no unique customer number in your dataset? If having a unique identifier is your problem and you do not want to assign a customer number you could "concatenate" the three name elements into a unique entry so as to distinguish the John L Sullivans from the Peter O Sullivans. And then you could just pivot by that name reference and take it form there.
posted by koahiatamadl at 10:08 PM on November 20, 2011

Response by poster: Okay, I see where I could concatenate in order to sort of combine the name back together. But then if I put that into a pivot table, wouldn't there still all be all the other unique transactions in there that I don't want to look at? Like, I could do a pivot table that read

ONENAME ="John L Sullivan" Count of Transactions="2" or SUM of Transactions="$930"

or what have you, plus a bajillion

ONENAME ="James T. Kirk" Count of Transactions="1" SUM of Transactions="$450"?

Because if I want to run some formulas to compare the data from the multi guys, I'd have to go put the date into another workbook and strip the singletons by hand. Right? (Wrong? Maybe?)
posted by Diablevert at 10:30 PM on November 20, 2011

Best answer: First, get something unique you can compare (you can use Concatenate as described above if you don't have anything better).

Next, in a new column determine if the unique field exists in more than 1 row. Assuming your unique value is in the A column, something like this:

=IF(COUNTIF(A:A,A1)>1,"Yes","No")

You should be able to just fill that down.

Now, you can use pages or filters to only run calculations on the Yeses.
posted by willnot at 10:48 PM on November 20, 2011

If you concatenate the names to get a unique(ish) anyway name field, you can then do the PivotTable as you describe in your answer.

You can then click the drop down arrow next to Count of Transactions, and then unselect the 1 box, it'll then only show people who had more than one transaction.
posted by chrispy108 at 2:31 AM on November 21, 2011 [1 favorite]

Best answer: strip the singletons by hand. Right?

No, use a COUNTIF formula on the whole column to see how many transactions each one has. If your names are in B, put this formula in A2: =COUNTIF(B:B,B2) and copy it down the whole column. Now copy all of column A, and Paste Special...Values over the same column. Now you can filter for "1" if you want to delete them or for everything but "1" if you want to copy them to another location.
posted by soelo at 10:42 AM on November 21, 2011

Response by poster: Thanks, all. Concantated and CountIfed and everything was cool.
posted by Diablevert at 1:37 PM on December 21, 2011