Highlight duplicates in a column using a conditional format

Quick little conditional format that I often use to find the duplicates in a table -

The formula I have used here is
Table.Filter(Column.Contains(thisrow.Column).Count() >1

12 Likes

Nice @mallika! I definitely use this all the time for deduping (but usually put this into the filter directly so I have only the remaining results that need to be scrubbed; filter ends up being lookup([thistable], Name, thisrow.Name).count() >1.

3 Likes

The real question is what in the world are you making that requires that much Absinthe?

3 Likes

Wouldn’t you like to know :smiley:
-spending the weekend working on sanitized examples-

The good news is I’m pretty sure most alcohols can be used as antibacterials

2 Likes

wondering if you can expand on this…

I’m trying to do this as a formula in the document that counts all the different names that are list more than once.

I can’t quite figure it out… can it be done @mallika?

Hi Tom, sorry I was traveling for a bit. Did you figure this one out?

sure didn’t! have been working on other things! If you have any insight that would be great though! hope you had an awesome trip :slight_smile:

The formula above should work for you - give you the number of times a a name has been listed and you can filter to remove the ones that are less than 1. Or use the filter Evan used.
However, I am happy to take a look at your particular document - share it with support@coda.io or mallika@coda.io and I can take a look.

(I did have a wonderful time traveling and am back ready to look at docs and more docs! )

Maybe because of some changes in Coda, I think there a couple things to update about this:

  • missing a close bracket here after “thisrow.Column)”
  • when trying to use this formula in my doc, it keeps autocorrecting out the “thisrow.”, and works without it

The new formula should read:
Table.Filter(Column.Contains(Column)).Count()>1

@mallika would mind updating the original post for others’ later reference?

1 Like

Thank you that was great help! It works for me but with a quirk.

I’d like to add some more detail for others that might come across this situation.
Seems like Table.Column and thisRow.Column are shown in the same way currently in Coda. But you have to make sure that you choose the correct version.

thisRow.Column for the first instance
image

And Table.Column for the second instance
image

1 Like

How would I tweak this to only highlight duplicates within the same group?

Only thing that worked for me was to use @evan’s filter in the conditional formatting.

@Guttorm_Flatabo Welcome!
I am glad you got the filter working. Do you want to elaborate on what did not work for you?