# Excel question **URL:** \ **Category:** Factual Questions\ **Created:** [July 6, 2012, 3:42pm UTC](https://boards.straightdope.com/t/excel-question/627419 "2012-07-06T15:42:01Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [July 6, 2012, 3:42pm UTC](https://boards.straightdope.com/t/excel-question/627419/1 "2012-07-06T15:42:01Z")
There used to be a time when I thought I knew what I was doing in Excel… Anyway, here’s what I want to do, as described by how I’ve been doing it. **Sheet 1** This is all the original data. It’s a list of transactions over 4 years, with funds going to various categories. Many people have made multiple transactions over each year. ```auto Year Name Fund Amount Type 2009 Adam House 100 A 2009 Adam Car 100 A 2009 Betty House 100 B 2010 Adam House 200 A 2010 Adam Car 50 A 2010 Betty House 100 B 2010 Betty Patio 500 B 2011 Adam House 200 A 2011 Betty House 300 B 2011 Betty Car 100 B 2011 Betty House 100 B 2011 Chuck Car 50 A 2011 David Patio 100 B 2012 Adam House 200 A 2012 Betty Car 100 B 2012 David Patio 100 B 2012 David House 100 B ``` **Sheet 2** This was data copied over from a pivot chart that consolidated all data from each person per year. The problem is that the “TYPE” field screws up the pivot, and I need to copy it in by hand (essentially). ```auto Year Name Amount Type 2009 Adam 200 A 2009 Betty 100 B 2010 Adam 250 A 2010 Betty 600 B 2011 Adam 200 A 2011 Betty 500 B 2011 Chuck 50 A 2011 David 100 B 2012 Adam 200 A 2012 Betty 100 B 2012 David 200 B ``` **Sheet 3** This is data that I can run easily from a pivot from Sheet 2. That much I know! So how would you generate Sheet 2? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 6, 2012, 5:38pm UTC](https://boards.straightdope.com/t/excel-question/627419/2 "2012-07-06T17:38:17Z")
I’m not sure I follow what you want exactly, but I _think_ you can do this with a pivot by setting it up like this: - Put Year, Name, Fund and Type in the Row Labels section - Put Sum of Amount in Values Section - For the field settings of the four fields in the rows as follows: — set Subtotals to None — set Layout to “Show item labels in tabular form” — check the box for “Repeat item labels” and leave the rest unchecked. This is Excel 2010, I’m don’t remember how the settings differed in older versions. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [July 6, 2012, 6:47pm UTC](https://boards.straightdope.com/t/excel-question/627419/3 "2012-07-06T18:47:40Z")
Interesting - those settings are very handy. So that changes my Sheet 3. How would you get the following: ```auto Year Type Persons Amount 2009 A 1 200 B 1 100 2010 A 1 250 B 1 600 2011 A 2 250 B 1 600 2012 A 1 200 B 2 300 ``` The problem that I was running into is that working from Sheet 1, it lists too many people, as it counts each transaction as a person, rather than summing up each person’s transactions, and counting it as one person. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 6, 2012, 7:23pm UTC](https://boards.straightdope.com/t/excel-question/627419/4 "2012-07-06T19:23:39Z")
Yup, that’s a problem with basic Pivot tables in Excel - you can’t do a distinct count. There are three options I can think of: 1. Add a new column to Sheet1 that has 0/1 values depending if the name/year/type is unique, then do a sum of that in your pivot table. See [here](http://www.pivot-table.com/excel-pivot-tables/unique-count-in-excel-pivot-table-with-powerpivot) for more details (“Pivot Table Data Workaround”). 2. Manually copy the pivot table from Sheet2 to a new sheet (Sheet2A) and create a new pivot table off that (you can’t create a pivot table based on pivot data, so the manual copy is necessary). This is a pretty ugly approach. 3. Get the [PowerPivot](http://www.microsoft.com/en-us/download/details.aspx?id=29074) add-in for Excel 2010. I haven’t used it a lot, but I know it does have DISTINCT COUNT capabilities.