Count unique values in Excel

[nickjar2]

Probably dead easy but we are stuck. We have a column in excel, say Column B. I need some code that will loop through the column until there is no more data, and also to not count duplicates. I have done this but usung quite a bit if code. Is there a built in func that will do this. eg.
Col B
1
2
3
4
4
5

The code would return just 5, NOT 6 ie. 4 is duplicated.

Thanks,

Nick

 

[psudeke]

Here's an array formula that will work, you can modify it in your code {=SUM(1/COUNTIF(F2:F21,F2:F21))} Just replace the range with the correct one. Good luck.

 

[Dreamboat]

Sorry, Nick. This one's all over the web. Do you ever use search engines for this stuff? I typed this into Yahoo:

"delete duplicates" excel

so that it would find Excel AND the exact phrase "delete duplicates".

Here's some good links:

http://www.pcmag.com/article/0,2997,s%3D1479%26a%3D5632,00.asp

http://www.barasch.com/excel/dedup.htm

Go here and look up duplicates for whichever specific task you want to perform:

http://www.cpearson.com/excel/topic.htm

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com