Data Lookup Question

  • Thread starter Thread starter Neel
  • Start date Start date
N

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.
 
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.
Click to expand...

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
 
Back
Top