Formula to check for duplicate values across six columns
I'm trying to create a formula to check for duplicate values across six columns. I have a formula that shows if columns 26 contain a duplicate of column 1. However, I need to check if columns 2 & 3 are duplicated, and so on and so forth. How can I modify the formula below to do this? Thanks!
=CONTAINS([Field of Study 1]@row, [Field of Study 2]@row:[Field of Study 6]@row)
Best Answers

Hey @Melinda Roy
Here's one approach. Unfortunately it isn't very eloquent, but it should work. This joins all the columns into a long text string then, using the OR statement, searches for each cell. If the response is more than 1, the formula will check the box. Thankfully your range was only 6 columns wide.
=IF(OR((LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 1]@row, ""))) / LEN([Field of Study 1]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 2]@row, ""))) / LEN([Field of Study 2]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 3]@row, ""))) / LEN([Field of Study 3]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 4]@row, ""))) / LEN([Field of Study 4]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 5]@row, ""))) / LEN([Field of Study 5]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 6]@row, ""))) / LEN([Field of Study 6]@row) > 1), 1)
Will this work for you?
Kelly

I ended up approaching this a different way and managed to figure out a solution! Here's what I did in case it's helpful for anyone else...
I created six helper columns and used the following formulas. I'm not sure this was the most efficient way, but it's working, so I'm happy.
Helper Column 1:
=IF([Field of Study 1]@row <> "", IF(COUNTIFS([Field of Study 1]@row:[Field of Study 1]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 2]@row:[Field of Study 2]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 3]@row:[Field of Study 3]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 4]@row:[Field of Study 4]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 5]@row:[Field of Study 5]@row, [Field of Study 1]@row)+ COUNTIFS([Field of Study 6]@row:[Field of Study 6]@row, [Field of Study 1]@row)> 1, 1)
Helper Column 2:
=IF([Field of Study 2]@row <> "", IF(COUNTIFS([Field of Study 1]@row:[Field of Study 1]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 2]@row:[Field of Study 2]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 3]@row:[Field of Study 3]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 4]@row:[Field of Study 4]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 5]@row:[Field of Study 5]@row, [Field of Study 2]@row)+ COUNTIFS([Field of Study 6]@row:[Field of Study 6]@row, [Field of Study 2]@row)> 1, 1)
Helper Column 3:
=IF([Field of Study 3]@row <> "", IF(COUNTIFS([Field of Study 1]@row:[Field of Study 1]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 2]@row:[Field of Study 2]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 3]@row:[Field of Study 3]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 4]@row:[Field of Study 4]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 5]@row:[Field of Study 5]@row, [Field of Study 3]@row)+ COUNTIFS([Field of Study 6]@row:[Field of Study 6]@row, [Field of Study 3]@row)> 1, 1)
etc. etc.
Answers

Hey @Melinda Roy
Here's one approach. Unfortunately it isn't very eloquent, but it should work. This joins all the columns into a long text string then, using the OR statement, searches for each cell. If the response is more than 1, the formula will check the box. Thankfully your range was only 6 columns wide.
=IF(OR((LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 1]@row, ""))) / LEN([Field of Study 1]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 2]@row, ""))) / LEN([Field of Study 2]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 3]@row, ""))) / LEN([Field of Study 3]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 4]@row, ""))) / LEN([Field of Study 4]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 5]@row, ""))) / LEN([Field of Study 5]@row) > 1, (LEN(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "))  LEN(SUBSTITUTE(JOIN([Field of Study 1]@row:[Field of Study 6]@row, " "), [Field of Study 6]@row, ""))) / LEN([Field of Study 6]@row) > 1), 1)
Will this work for you?
Kelly

Hi Kelly  thanks for the quick response! Your formula works great if every column (16) has a value. Is it possible to modify the formula so it recognizes duplicates if some of the columns are blank? For example, we may have some rows where only columns 13, columns 14, etc. have a value. I appreciate your help with this!

I ended up approaching this a different way and managed to figure out a solution! Here's what I did in case it's helpful for anyone else...
I created six helper columns and used the following formulas. I'm not sure this was the most efficient way, but it's working, so I'm happy.
Helper Column 1:
=IF([Field of Study 1]@row <> "", IF(COUNTIFS([Field of Study 1]@row:[Field of Study 1]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 2]@row:[Field of Study 2]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 3]@row:[Field of Study 3]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 4]@row:[Field of Study 4]@row, [Field of Study 1]@row) + COUNTIFS([Field of Study 5]@row:[Field of Study 5]@row, [Field of Study 1]@row)+ COUNTIFS([Field of Study 6]@row:[Field of Study 6]@row, [Field of Study 1]@row)> 1, 1)
Helper Column 2:
=IF([Field of Study 2]@row <> "", IF(COUNTIFS([Field of Study 1]@row:[Field of Study 1]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 2]@row:[Field of Study 2]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 3]@row:[Field of Study 3]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 4]@row:[Field of Study 4]@row, [Field of Study 2]@row) + COUNTIFS([Field of Study 5]@row:[Field of Study 5]@row, [Field of Study 2]@row)+ COUNTIFS([Field of Study 6]@row:[Field of Study 6]@row, [Field of Study 2]@row)> 1, 1)
Helper Column 3:
=IF([Field of Study 3]@row <> "", IF(COUNTIFS([Field of Study 1]@row:[Field of Study 1]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 2]@row:[Field of Study 2]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 3]@row:[Field of Study 3]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 4]@row:[Field of Study 4]@row, [Field of Study 3]@row) + COUNTIFS([Field of Study 5]@row:[Field of Study 5]@row, [Field of Study 3]@row)+ COUNTIFS([Field of Study 6]@row:[Field of Study 6]@row, [Field of Study 3]@row)> 1, 1)
etc. etc.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives