formula question

Chevon Brownell

we are using smartsheet to keep track of community park passes. i have a column for name, account #, tag # and pass #. I need to be notified / alerted if an account gets more than one pass. Is there a formula that can check for a duplicate account number when the pass # cell is not blank? In the below picture, I sorted by division-block-lot; however, normally this will not be sorted in this way. I wanted to show that 6-39 was issued 2 different Annual Passes and should not have. I need an alert that will help prevent this, if possible.

Thank you in advance.

Mark Cronk

Excellent. Let me know if it doesn't. Please accept and answer to close the discussion. Thank you for contributing to the community.

Mark

Mark Cronk

Hi Chevron,

Add a checkbox column [duplicate]with the formula:

=IF(COUNTIFS([annual pass #]:[annual pass #], IS TEXT(@cell) [division-block-lot]:[division-block-lot], [division-block-lot]@row)>1, 1, 0)

The box will check if the row is duplicate.

Set an automation that if [duplicate] is checked send an alert. Both rows will check when duplicate.

Work?

Mark

Chevon Brownell

I think this will work. Thank you.

Mark Cronk

Excellent. Let me know if it doesn't. Please accept and answer to close the discussion. Thank you for contributing to the community.

Mark