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!

Andrée Starå

Hi @Hatley

I hope you're well and safe!

It's correct because the DISTINCT function works because it only counts/includes the value once.

Try something like this in a Checkbox or Flag type column. (drag down to fill the column)

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

 Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅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!

Hatley

That came back with "Incorrect Argument". Hmm.

Andrée Starå

@Hatley

Did you start on row 1 and then drag it down?

Hatley

Yes, I did drag the formula down.

Note: I changed the referenced column name to remove the parenthesis, in case that was causing problems, and I subsequently did update your suggested formula with the new column name to remove the parenthesis as you can see in the picture below.

Andrée Starå

@Hatley

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Hatley

Basically I'm trying to set up a helper column so I can "flag" any serial numbers (via conditional formatting or workflow update requests) if they are a duplicate serial # entered into the same Primary column. Each row's entry/serial # should be a unique entry.

Andrée Starå

@Hatley

Apologies!

Try this instead.

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

Did that work?

Hatley

Close. It worked in actuality. The duplicate showed a 1, but the original row shows a blank.

What if I want all instances of the duplicate records to have attention drawn to them so I can list that they're duplicates of each other? Basically I want the user to look at both records to reconcile the duplicates and differences.

All duplicates, even if they're duplicates of different/multiple records, can have the same formatting. Would be nice if I could color/reference each duplicate to link to each other for quick locating of the duplicates, but I know that's probably impossible or at least rather complicated!

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!

Hatley

Yes, that worked. Thank you so much!

Andrée Starå

@Hatley

Excellent!

You're more than welcome!

✅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!