Find unique values in column - in a SINGLE CELL FORMULA; can it be done please?

Hi all,

On the attached sheet I have a column of emails (in my real version, this column could contain up to 500k or more emails).

I know how to count the number of emails in the column BUT many are doubled.

I want to add a SINGLE CELL FORMULA in cell D2 which will count the number of unique email values in the entire column A.

I don't want to add a filter in column B and count that.

Can this be done please? Played with several SUMPRODUCT ideas via Google but most 'freeze' up due to process weight.

Thanks in advance,

Ian

Try

=SUMPRODUCT(1/COUNTIF(A2:A30,A2:A30))

@sweep - I tried this one on my live data set;

Formula:

Please Login or Register  to view this content.

And the sheet just hung while calculating (note - my live data, the emails are in column C but I did make all changes, to no avail).

OOI - what does the 'zzzzz' represent?

Hi Ian - I know you've asked for a formula in a single cell and other contributors are attempting that, but I've come up against this issue before and didn't get anywhere with formulae.

This snippet of code will work, though, and it shouldn't matter too much how many rows are involved.

Please Login or Register  to view this content.

@thiefofthings - thanks for this - I suspected that the formula path may be tricky so thanks for confirming what Google results had me fearing.

If I want to run this on column C, as opposed to A, just need to alter the column references to A in the VBA yes?

Originally Posted by iantix

@thiefofthings - thanks for this - I suspected that the formula path may be tricky so thanks for confirming what Google results had me fearing.

If I want to run this on column C, as opposed to A, just need to alter the column references to A in the VBA yes?

Yep, just change from A to C, and you end up with this:

Please Login or Register  to view this content.

And it works perfectly! Thank you @thiefofthings - had to use column ZZ instead of M (which is used on my real sheet).

no worries