View Full Version : Excel question

07-06-2012, 11:42 AM
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.

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

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?

07-06-2012, 01:38 PM
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.

07-06-2012, 02:47 PM
Interesting - those settings are very handy.

So that changes my Sheet 3. How would you get the following:

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.

07-06-2012, 03:23 PM
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.

Send questions for Cecil Adams to: cecil@straightdope.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Copyright 2018 STM Reader, LLC.