Help with multiple If statements checking multiple columns for duplicates

Options

Hello,

I am trying to check one column of data ([Duplicate Donor ID Check]) against multiple columns (two examples being [RM Donor ID], [SS Donor ID] but there will be more) of data for duplicates.

The formula works when checking [Duplicate Donor ID Check] against itself for duplicates returning the result of "1" but it doesn't go through the rest of the If statements until the final "value_if_false", which is "0". Depending on which column the duplicate is found, I want the formula to return a different number so I know where it is. I have the AND in the formula to not count blanks as duplicates.

Here is what I have:

=IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([Duplicate Donor ID Check]:[Duplicate Donor ID Check], [Duplicate Donor ID Check]@row) > 1), 1, IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([RM Donor ID]:[RM Donor ID], [Duplicate Donor ID Check]@row) > 1), 2, IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([SS Donor ID]:[SS Donor ID], [Duplicate Donor ID Check]@row) > 1), 3, 0)))

Adding a snip if that helps

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    edited 05/10/24 Answer ✓
    Options

    Hey @Mmadd,

    NVM I understand what you're trying to do now! I think I was able to get it working by changing the COUNTIFS statements:

    =IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([Duplicate Donor ID Check]:[Duplicate Donor ID Check], [Duplicate Donor ID Check]@row) > 1), 1, IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([RM Donor ID]:[RM Donor ID], [Duplicate Donor ID Check]@row) >= 1), 2, IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([SS Donor ID]:[SS Donor ID], [Duplicate Donor ID Check]@row) >= 1), 3, 0)))

    Try that and see if it works!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    edited 05/10/24 Answer ✓
    Options

    Hey @Mmadd,

    NVM I understand what you're trying to do now! I think I was able to get it working by changing the COUNTIFS statements:

    =IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([Duplicate Donor ID Check]:[Duplicate Donor ID Check], [Duplicate Donor ID Check]@row) > 1), 1, IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([RM Donor ID]:[RM Donor ID], [Duplicate Donor ID Check]@row) >= 1), 2, IF(AND([Duplicate Donor ID Check]@row <> "", COUNTIFS([SS Donor ID]:[SS Donor ID], [Duplicate Donor ID Check]@row) >= 1), 3, 0)))

    Try that and see if it works!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Mmadd
    Options

    Hi @bisaacs,

    That did the trick. Thank you for your help!