I need to identify duplicate entries across columns on one row

Hi,

I have a Crew list sheet for Sports TV Production. I have the positions listed in one column, and a column for the Crew Names for Game 1, Game 2, Game 3 and game 4.

I need to identify duplicate entries across the columns for Games 1 - 4. I think I'd need a "helper" column for each of my "game" columns. I've been trying to use this =IF(COUNTIFS([Game 1]:[Game 1], [Game 1]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row) > 1, 1) or variations on that formula but it seems that it is only looking for duplicates in the Game 1 Column.

Is there a formula that looks for duplicates across different columns?

Ultimately, I want to show all of the "non-dupes" in a report where my production team can leave comments on crew performance without a multitude duplicates.

I've attached a photo of my Crew sheet template

Find more posts tagged with

Formulas

Accepted answers

All comments

Brian_Richardson

You could put the Game columns next to each other and then setup a checkbox with this formula:

=IF(COUNTIFS([Game 1]:[Game 4], <>"", [Game 1]:[Game 4], OR(@cell = [Game 1]@row, @cell = [Game 2]@row, @cell = [Game 3]@row, @cell = [Game 4]@row)) > 4, true)

This will check if any of the names in Game 1 - Game 4 are duplicates with any other row, or same row.

I highlighted the duplicate names. You won't be able to highlight the individual duplicates through conditional formatting though, since the formula checks across all columns.

If you need to check on a per-game basis then you'll need a checkbox for each column slightly edited to a simpler formula like

=IF(COUNTIFS([Game 1]:[Game 1], <>"", [Game 1]:[Game 1], [Game 1]@row) > 1, true)

You could also then use conditional formatting to highlight the specific duplicate names.

jmyzk_cloudsmart_jp

Hi @DMCARBRAY

You can use the DISTINCT function to count unique names and mark Y if the count is less than 4.

[Duplicates Y/N]=IF(COUNT(DISTINCT([Game 1]@row, [Game 2]@row, [Game 3]@row, [Game 4]@row)) < 4, "Y", "N")

For the columns range whose columns do not position next to each other, you can connect them by "," as shown in the formula example above.

https://app.smartsheet.com/b/publish?EQBCT=5b5a2fe63d54479b94a55286de9ea408 (Link to published demo sheet)

To make it easy to identify which Games are duplicates, I added G1 to G4 so that you can use them in the conditional formatting.

[G1] =IF(COUNTIF([Game 1]@row:[Game 4]@row, [Game 1]@row) > 1, 1)

[G2] =IF(COUNTIF([Game 1]@row:[Game 4]@row, [Game 2]@row) > 1, 1)

[G3] =IF(COUNTIF([Game 1]@row:[Game 4]@row, [Game 3]@row) > 1, 1)

[G4] =IF(COUNTIF([Game 1]@row:[Game 4]@row, [Game 4]@row) > 1, 1)

Then, you can use conditional formatting that looks like this;

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.