Formula to generate autonumber if cell is blank and another column contains specific phrase

Angela Campbell

I am looking for assistance with a formula looks at Coulmn1 to see if it contains one of the drop down choices and the column the formula is in does not include a name then a number will auto-generate. For example:

=IF([Please select from the responses below:]@row, CONTAINS("I like rain")@cell, AND([Name: is optional]@row, @cell, ISBLANK(autogenerate)

Thank you in advance for any assistance.

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.

Angela Campbell

Actually, I was able to find a formula that works for my specific need and the above can be disregarded. However, my new challenge is that the formula only recognizes duplicates and does not leave out the first duplicate.

EX:

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

Mark Cronk

Hi Angela, I saw you posted hi second question as it's own thread and responded there. 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.