Count Unique Values

[paulycr]

Hello,

I have a spreadsheet where column A represents a person (1-242) and column B represent a choice that person made (1-5). Each person in column A gets to make 12 choices (i.e. there are 12*242=2904 rows). I would like a formula that counts the number of unique choices each person makes. In the example below the unique count for person 1 would be 4, the unique count for person 2 would be 3.... etc.

Thanks in advance for any assistance.

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Person[/TD]
[TD="width: 64"]Choice[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi and welcome to MrExcel,

try this:

Book1
	A	B	C	D	E
1	Person	Choice		Person	# Choice
2	1	1		1	4
3	1	1		2	3
4	1	2		3	2
5	1	2
6	1	2
7	1	3
8	1	3
9	1	3
10	1	3
11	1	4
12	1	4
13	1	4
14	2	1
15	2	1
16	2	1
17	2	2
18	2	2
19	2	2
20	2	2
21	2	3
22	2	3
23	2	3
24	2	3
25	2	3
26	3	1
27	3	1
28	3	2
Sheet1

Cell Formulas
Range	Formula
E2	{=SUM(IF(FREQUENCY(IF($A$2:$A$28=D2,$B$2:$B$28),IF($A$2:$A$28=D2,$B$2:$B$28))>0,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

@ jorismoerings

I believe one if clause should suffice, i.e.

=SUM(IF(FREQUENCY(IF($A$2:$A$28=D2,$B$2:$B$28),$B$2:$B$28),1))

Do we have any counterevidence?

 

[jorismoerings]

@Aladin Akyurek,

You're right, one IF clause within FREQUENCY is sufficient as well. Mine will limit the BINS_array while yours take the full range.
Do not exactly know what the impact of this (in calculating time) will be as the data range grows to number of records as mentioned.

 

[Aladin Akyurek]

@Aladin Akyurek,

You're right, one IF clause within FREQUENCY is sufficient as well. Mine will limit the BINS_array while yours take the full range.
Do not exactly know what the impact of this (in calculating time) will be as the data range grows to number of records as mentioned.

FREQUENCY numeric to numeric is very fast, faster than filter(X) to X than filter(X) to filter(X)...

 

[paulycr]

Both methods worked perfectly, thanks to both of you for taking the time to help.

Regards,
Paul