Duplicate

Angela Campbell

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")

Angela Campbell

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.

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

Angela Campbell

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.