Pivot Table Distinct Count Hack, Is My Logic Sane? 1

[anationalacrobat]

The data we're tracking is for community services. We have multiple communities and community members who would take part in a class on a particular date. So our data looks like this:

date participant class community
1-1-09 joe blow underwater basketweaving Happyville
1-3-09 joe blow underwater basketweaving Happyville
1-3-09 jane blow underwater basketweaving Happyville
2-1-09 joe blow underwater basketweaving Happyville
2-2-09 joe blow charades Happyville

As it stands, pivot tables can sort this data wonderfully, grouping by community, class, date, and participant. But we're falling apart when it comes to distinct counts.

I want it to be able to tell me:

Jan
Underwater Basketweaving, 2 attendees
Feb
Underwater Basketweaving, 1 attendee
Charades, 1 attendee

Right now the Pivot table can tell me that Joe Blow and Jane Blow are in underwater basketweaving in January but I'll get a count of 3 regardless of what I put in the values box. That's because Joe went twice and Jane went once. That's my total number of sits in the class and it's important but I also need to know distinct attendees per month. Hmmmmm.

Now when I'm working with static data, I'll sort everything ascending order and do tests. I'll take row 2 and do a comparison against row 1 -- if month, participant, and class are not equal, print 1, else print null. This way if a duplicate value is below, it prints nothing. Now I have my distinct count.

Given that I'm working with dynamic data, more entries can be entered at any time and I can't count on the people doing data entry to sort everything the way my little formulas are anticipating. I've got an inkling that something like this could be accomplished with database lookups but I'm not quite sure. Is this even the right avenue to be walking down or should I be approaching it in some other fashion?

 

[Fenrirshowl]

I think you can do it IF you trust your people to copy 2 formulae down next to the data they enter.

1) you need a unique identifier/string column for the instance you are trying to illustrate - Given your example and given you are summarising by months I would suggest =Month(date)&year(date)&Participant&Class&Community

Producing something like:
12009joe blowunderwater basketweavingHappyville

2) Say the above starts in cell E2. Then you need an IF/Countif function: =IF(COUNTIF($E$2:E2,E2)=1,1,0) and copy it down. This gives you your unique count.

Then you can include this column in your Data field (as a Sum rather than a Count field), and you should get your unique entries, with the correct summation as a total.

Fen

 

[anationalacrobat]

I'm using the tables feature in Excel 2007 which actually works surprisingly well. Drag the handle down on the right, all formulae are copied automatically.

Ok, I'll give that a shot and keep everyone posted as to the results! Thanks a bunch.

 

[Fenrirshowl]

No worries. I hope it works out ok.

Fen

 

[anationalacrobat]

Ok, it appears to be working! I'm just trying to figure out how.

=IF(COUNTIF($E$2:E2,E2)=1,1,0)

Ok, I see. The top of the compared range is pinned as an absolute reference, E2. So this formula is saying:

Compare the range "Very top of column to the line I'm on, see if the value at the bottom has occurred anywhere else in the column. If it has appeared only once, print a one, else print a zero to show it's occurred more than one time."

Tricksy!