Cumulative unique count - Excel 2010

[excell_hell]

Having difficulty solving this one:

I have a large spreadsheet, but am mostly interested in 2 columns of data (both text fields). A simplified example below:

Teacher	Student ID
Sally	1234K
John	5678K
Sally	1234K
Peter	1234K
Peter	5678K
Sally	8765K
John	1234K
John	8765K
John	5678K

<tbody>
</tbody>

I initially just needed to count unique students per teacher. This was fine, eg. Sally - 2, John - 3, Peter - 2.

What I need now is a cumulative unique count, based on an order I define. So if I chose the order Peter, Sally then John, the unique counts would be as follows:
Peter: 2
Sally: 1
John: 0
= 3, as there is 3 unique combinations in total.

I need to be able to change the order of teacher to test some theories, so it needs to be based on cell references. Also there is approx 100 "teachers" and 10,000 "students". I have the data in one worksheet, and am doing the counting/ordering on another worksheet.

Help!? It's probably straight forward, but I am quite new to formulas used for unique counts and I can't work it out.

I need it via formula not through a pivot table.

Any ideas? If you need more info let me know.

 

[XOR LX]

Hi,

Forgive me if I'm missing the obvious, but I cannot work out how you arrive at your desired results of 2, 1 and 0 based on your chosen order: could you offer a brief explanation?

Regards

 

[excell_hell]

Sorry, I probably should have spelled it out better. Basically they are unique student ID counts, BUT, if the student ID has already been counted in another teacher, don't count it again. There is only 3 unique Student IDs, so no matter what order I count the teachers in, the total should always be 3.

So in my example order (Peter > Sally > John):
Peter gets 2 (1234K, 5678K)
Sally only gets 1 for 8765K (as 1234K has already been counted in Peter)
John gets none because all student IDs have already been counted in Peter & Sally.

Does this make sense?

 

[XOR LX]

Ok, then assuming that your original data table is in the range A1:B10 (with headers in row 1), and that you put Peter, Sally and John in D2:D4, enter this array formula** in E2:

=SUM(IF(FREQUENCY(IF($A$2:$A$10=D2,MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),1))

Then, in E3:

=SUM(IF(FREQUENCY(IF($A$2:$A$10=D3,IF(ISNA(MATCH($B$2:$B$10,IF(ISNUMBER(MATCH($A$2:$A$10,D$2:D2,0)),$B$2:$B$10),0)),MATCH($B$2:$B$10,$B$2:$B$10,0))),ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),1))

Copy this latter to E4 (and obviously beyond if necessary).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[excell_hell]

Thank you! I'm going to try it first thing at work tomorrow. Will let you know how I go.

 

[XOR LX]

Ok. Just noticed a space had somehow slipped into the second formula when I posted. Should be of course:

=SUM(IF(FREQUENCY(IF($A$2:$A$10=D3,IF(ISNA(MATCH($B$2:$B$10,IF(ISNUMBER(MATCH($A$2:$A$10,D$2:D2,0)),$B$2:$B$10),0)),MATCH($B$2:$B$10,$B$2:$B$10,0))),ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),1))

Regards

 

[XOR LX]

Hmmm. The space persists, despite not showing in the formula when I go to edit it...

 

[excell_hell]

Thanks XOR LX, it worked perfectly and you saved my butt! When I get a free moment I'm going to try to actually understand how it works

 

[XOR LX]

You're welcome! And come back in a few days if you still can't figure out how that formula works!