Count unique combinations

I have the following dataset:

ColA-ID ColB-Yr
6101920 1984
6101920 1985
7812235 2001
7812235 2002
8812345 1960
8812345 1960

I'm hoping someone can here help me figure out what I'm assuming is a simple macro would look like if I wanted it to give me the number of unique combinations of ID & Yr in the rows of data shown above. The expected result would be 5 (from a total of 6 shown) and it would be shown in another cell within the same worksheet.

Thanks for your help!

Why use VBA code when this can be done quickly using a Pivot Table?

If needed, one formula approach might be:

Please Login or Register  to view this content.

(assumes pre 2007 version per profile)

Hi,

No need for code:

In C1 enter =A1&B1

copy down the length of your data

then use this array (using CTRL, SHIFT and ENTER) to count unique values.

=SUM(1/COUNTIF(C1:C5,C1:C5))

adjust the range C1:C5 to suit