excell_hell
New Member
- Joined
- Feb 10, 2014
- Messages
- 4
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Teacher[/TD]
[TD]Student ID[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5678K[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]5678K[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]8765K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]8765K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5678K[/TD]
[/TR]
</tbody>[/TABLE]
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.
I have a large spreadsheet, but am mostly interested in 2 columns of data (both text fields). A simplified example below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Teacher[/TD]
[TD]Student ID[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5678K[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]5678K[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]8765K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1234K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]8765K[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5678K[/TD]
[/TR]
</tbody>[/TABLE]
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.