Duplicate Checking of Multiple Dropdown Values with criteria

Donna B.

Hello guys! I have these conference registrations at which multiple attendees can have different conference badge. The badge column allows multiple entries. My goal is to identify if an attendee has selected the same badge in multiple registration:

E.g. Attendee Marcus Schabacker has entered 2 registrations, both registrations have "ANS" and "PUB" badges. How do I get to flag this type of duplicate?

Paulo Ferrer

Not sure if I understood, but maybe the function "HAS" can help you.

With "HAS" you can check if there is a value match inside a cell with multiple values from a dropdown list. By matching the names with the items available in the dropdown list you will be able to identify duplicates.

Donna B.

Hi @Paulo Ferrer , I am trying to flag if same name has the same badge:

Marcus has appeared twice and has the same "AAA" badge - among other badges in the same dropdown cell. So, both row where Marcus appeared to select "AAA" should be flagged red.

Leibel S

@Donna B.

Add column called 'CountBadges':

=COUNTM(Badges@row)

Add column called 'All Badges' (Multi select dropdown):

=JOIN(COLLECT(Badges:Badges, [Full Name (Duplicate Checking)]:[Full Name (Duplicate Checking)], [Full Name (Duplicate Checking)]@row), CHAR(10))

Add column called 'Duplicate Badge':

=IF(SUMIFS(CountBadges:CountBadges, [Full Name (Duplicate Checking)]:[Full Name (Duplicate Checking)], [Full Name (Duplicate Checking)]@row) > COUNTM([All Badges]@row), 1, 0)

Leibel S

@Donna B.

So you are aware, this duplicate flag will end up highlighting this persons name every time he is on the list (regardless if the current row has the duplicate badge or not).

there is a way around this (quite complicated) check out this thread:

https://community.smartsheet.com/discussion/74520/highlight-duplicate-values-with-a-criteria-within-multiple-select-column