I have a column of data with 10,000 data items with some repeats. I want to only pull out the first instance of each of these and do a count of them.

Ideally, I should have a function to input a 1 next to the first instance of each item. I can use that whole data set in a pivot table and count the number if 1's to find out the unique instances.

I could pull out unique instance via the advanced filter but that won't work with the pivot table.

Can anyone help me out with this?

Thanks,
Neel.

Hi Neel,

Am Mon, 30 Jul 2012 11:33:08 -0700 (PDT) schrieb Neel:

I have a column of data with 10,000 data items with some repeats. I want to only pull out the first instance of each of these and do a count of them.

Ideally, I should have a function to input a 1 next to the first instance of each item. I can use that whole data set in a pivot table and count the number if 1's to find out the unique instances.

I could pull out unique instance via the advanced filter but that won't work with the pivot table.

your data in column A. Then for counting unique instances try:
=SUMPRODUCT((A1:A10000<"")/COUNTIF(A1:A10000,A1:A10000&""))

Or write in B1:
=--(COUNTIF($A$1:A1,A1)=1)and drag down
Count of unique instances is SUM(B:B)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2