Data Lookup Question

[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.

Can anyone help me out with this?

Thanks,
Neel.

 

[Claus Busch]

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