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.

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

Back
Top