Formula to check for duplicate values across six columns

Melinda Roy

I'm trying to create a formula to check for duplicate values across six columns. I have a formula that shows if columns 2-6 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)

Kelly Moore

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

Melinda Roy

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.

Kelly Moore

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

Melinda Roy

Hi Kelly - thanks for the quick response! Your formula works great if every column (1-6) 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 1-3, columns 1-4, etc. have a value. I appreciate your help with this!

Melinda Roy

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.