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

Options

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.


Best Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    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)


    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Sameer
    Sameer ✭✭
    Answer ✓
    Options

    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,

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    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.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Sameer
    Sameer ✭✭
    Options

    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
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    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)


    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Sameer
    Sameer ✭✭
    Answer ✓
    Options

    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
    Ryan Sides ✭✭✭✭✭✭
    Options

    Glad you got it working!

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!