How to set a formula for 3 different values to prevent any duplication

Sameer

Hello Everyone,

I hope all are doing well.

I'm facing an issue to find the suitable formula to my case taking into consideration i'm a newbie here in smartsheet and would appreciate your kind support.

The formula i'm looking for is targeting 3 main columns : Start Date, End Date & Facilitator.

To assure that if a duplication found (if a facilitator has been assigned to the same start and end dates) to mark the Health column as red. Otherwise, to mark it as green.

Plus : The formula which i'll be trying will be placed the same for the 2 other different parts. And will only be added to the "4 Day Workshop + 1 Day Reinforcement ..etc) as per the below picture.

Thank you and looking forward to your valuable support.

Ryan Sides

Hi @Sameer you can check this sheet: https://app.smartsheet.com/b/publish?EQBCT=2acf94e49e11448a8277c246229c8fb4

Formulas used:

Key column: =JOIN([Start Date]@row:Facilitator@row)

Check column: =IF(COUNTIF(Key:Key, Key@row) > 1, 1, 0)

Sameer

Hi @Ryan Sides ,

Thank you so much for the explaining how we can get this done.

I've indeed created 2 new columns as you've mentioned : 1) Key - 2) Check

Where i JOIN all the 3 different cells together in the "Key" Column, and then use the formula to Check if there is any duplicate if the same Value in the "Key" to be representing the Column "Check" (1 or 0)

Then i've used the following formula in the Health Column =IF(Check@row > 0, "Red", "Green") to automatically mark the cell based on whether there is a duplication by Red (if check is 1) and Green (if check is 0)

Thank you,

Ryan Sides

Hi @Sameer , you'll need to create 2 new columns.

1) A "Key" column where you use the function JOIN to join the 3 columns together.

2) A "Check" column where you use the Key column to count any instances of that Key@row in the Key Column. If the count is greater than 2, then check the checkbox with "1". Then you can set your Conditional Format to highlight any rows where the Check column is checked.

Sameer

Hi @Ryan Sides , Trusting you are keeping well.

Thank you for your reply. Could you please explain more on how this can be done, or if there is a reference i can relate to?

In addition, may i know the formula needed to be placed?

Ryan Sides

Hi @Sameer you can check this sheet: https://app.smartsheet.com/b/publish?EQBCT=2acf94e49e11448a8277c246229c8fb4

Formulas used:

Key column: =JOIN([Start Date]@row:Facilitator@row)

Check column: =IF(COUNTIF(Key:Key, Key@row) > 1, 1, 0)

Sameer

Hi @Ryan Sides ,

Thank you so much for the explaining how we can get this done.

I've indeed created 2 new columns as you've mentioned : 1) Key - 2) Check

Where i JOIN all the 3 different cells together in the "Key" Column, and then use the formula to Check if there is any duplicate if the same Value in the "Key" to be representing the Column "Check" (1 or 0)

Then i've used the following formula in the Health Column =IF(Check@row > 0, "Red", "Green") to automatically mark the cell based on whether there is a duplication by Red (if check is 1) and Green (if check is 0)

Thank you,

Ryan Sides

Glad you got it working!