Duplicate

Hello,

I was able to find a formula that works to ignore blank cells and identify duplicates but the first entry is also being labeled a duplicate. Is there a way to ignore the first entry and only add duplicate to all proceeding?


=IF(AND([Column Name]@row <> "", COUNTIFS([Column Name]:[Column Name], [Column Name]@row) > 1), "Duplicate")

Best Answer

  • Angela Campbell
    Answer ✓

    Hi Mark,

    Thank you for your feedback. I am actually looking for something that is a column formula. I am not looking for one specific duplicate because there may be many.

    The actual [Column Name] is looking at email addresses to identify if there are any duplicates (not including blanks)

    True formula (not including private information, so some characters are left out)

    =IF(AND([Email: I do not want vaccine]@row <> "", COUNTIFS([Email: I do not want vaccine]:[Email: I do not want vaccine], [Email: I do not want vaccine]@row) > 1), "Duplicate")

    The email is not always mandatory which is why I need to be able to leave blank cells alone and if there is a true duplicate I need to be able to see the original (or one of) the submissions to be able to not only count number of submissions but as well as use automation to move duplicates to an archive sheet and not count as a true number for dashboard calculations.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Angela,

    The formula below will do what you ask but can't be used as a column formula:


    =IF(AND([Column Name]@row <> "", COUNTIFS([Column Name]$1:[Column Name]@row, [Column Name]@row) > 1), "Duplicate")

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Angela Campbell
    Answer ✓

    Hi Mark,

    Thank you for your feedback. I am actually looking for something that is a column formula. I am not looking for one specific duplicate because there may be many.

    The actual [Column Name] is looking at email addresses to identify if there are any duplicates (not including blanks)

    True formula (not including private information, so some characters are left out)

    =IF(AND([Email: I do not want vaccine]@row <> "", COUNTIFS([Email: I do not want vaccine]:[Email: I do not want vaccine], [Email: I do not want vaccine]@row) > 1), "Duplicate")

    The email is not always mandatory which is why I need to be able to leave blank cells alone and if there is a true duplicate I need to be able to see the original (or one of) the submissions to be able to not only count number of submissions but as well as use automation to move duplicates to an archive sheet and not count as a true number for dashboard calculations.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!