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
Answers
-
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.
-
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.
(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;
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!