Count unique items in column.

**maacmaac** · 06-18-2008, 05:43 PM

I am trying to count the number of unique entries in column F of worksheet with ~30,000 rows and I am using Excel 2000. I tried using a pivot table but keep getting an error becuase of too much data.

I orginally posted in functions forum but didn't get any response so I am thinking I posted in wrong forum and this is a programming issue that needs a macro.

https://www.excelforum.com/showthread.php?p=647266

Any comments are really appreciated. If there is no solution, please let me know. Thanks.

**VBA Noob** · 06-18-2008, 05:52 PM

Maybe

=SUMPRODUCT((A1:A1000<>"")/COUNTIF(A1:A1000,A1:A1000&""))

Note Sumproduct won't work for a whole column

VBA Noob

**mikeyfear** · 06-18-2008, 05:57 PM

something like:

Please Login or Register  to view this content.

...borrowed from bob at the oz forum

it doesnt much like 30000 lines tho, i use something similar on list of postcode and it grinds my machinie to a halt.

*will have to be quicker digging out those examples in future*

VBA solution

Please Login or Register  to view this content.

**maacmaac** · 06-18-2008, 06:22 PM

Formula works perfect. Exactly what I needed. One more question on the formula.

At the end of the formula is

Please Login or Register  to view this content.

What is this part of the formula used for (the &"")? I took this part out of the formula and it still appears to be working correctly.

Thanks

**daddylonglegs** · 06-18-2008, 06:26 PM

This is probably more efficient for 30000 rows......

F1 needs to have a header, e.g. "names", data to be counted in F2:F30000

In D1 enter "criteria" [without quotes]
In D2 enter this formula

=COUNTIF($F$2:F2,F2)=1

and then in D3 enter this formula to count uniques

=DCOUNTA($F$1:$F$30000,1,$D$1:$D$2)

**maacmaac** · 06-19-2008, 10:14 AM

All solutions presented are working. Thanks to all for your help.

Count unique items in column.

LinkBack

Thread Tools

Rate This Thread

Display

Count unique items in column.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions