Data Lookup Question

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top