Count Distinct Values in a Column

Hatley

I have a serial # column that I am trying to create a "helper" column to verify that the serial # entered is unique - to avoid/flag duplicate entries with conditional formatting later.

I have tried this formula in the blue highlighted cell pictured below:

=COUNTM(DISTINCT(([Computer Serial # (Parent Asset)]:[Computer Serial # (Parent Asset)])))

It comes back with a "1", when it should be a "2" since the serial # is duplicated in row 2.

I have also tried this formula with the same incorrect answer of "1":

=COUNT(DISTINCT(COLLECT([Computer Serial # (Parent Asset)]:[Computer Serial # (Parent Asset)], [Computer Serial # (Parent Asset)]:[Computer Serial # (Parent Asset)])))

Is this a glitch, or am I doing something wrong?

Andrée Starå

@Hatley

Ah ok.

Try this instead.

=IF([Computer Serial # Parent Asset]@row = "", 0, 
IF(COUNTIF([Computer Serial # Parent Asset]:[Computer Serial # Parent Asset], 
[Computer Serial # Parent Asset]@row) > 1, 1))

Did that work?

Yes, you're correct that it would be complicated to show different groups of duplicates.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!