Formula to check for duplicate values across six columns

Options

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)

Tags:

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Melinda Roy ✭✭✭
    Answer ✓
    Options

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Melinda Roy ✭✭✭
    Options

    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
    Melinda Roy ✭✭✭
    Answer ✓
    Options

    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.