Excel question

Factual Questions
1

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?

2

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.

3

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.

4

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 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 add-in for Excel 2010. I haven’t used it a lot, but I know it does have DISTINCT COUNT capabilities.