Count Instances of a Difference

[ExcelMacLean]

Hey - I'm not sure how to accurately describe this question in the title, but here it goes.

I have two columns with data, and one review column. I am looking for help creating a worksheet formula which will count how many unique values in column 2 are associated with the value in column 1.

In the example below, Bob has only 1 unique value associated with his name in column 2, "A," so the expected result is "1." Dave has two unique values associated with this his name, both "B" and "C," so the expected result is "2."

I am looking for a regular old worksheet function, no VBA this time. I greatly appreciate any help you can provide - thanks in advance!

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Review[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]C[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]C[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

You can use this array formula in C2

=SUM(IF(FREQUENCY(IF(A$2:A$6=A2,MATCH(B$2:B$6,B$2:B$6,0)),ROW(B$2:B$6)-ROW(B$2)+1),1))

confirm with CTRL+SHIFT+ENTER and copy down column

 

[ExcelMacLean]

Hey, thank you for the reply. I forgot to mention my actual file is about 670,000 rows.

- Do you think an Array Formula would crash my Excel?
- Is it possible to get the same functionality out of a non-Array function?

Thanks!