How to alert regarding a duplicate value within a row?

Hi I would like to add some sort of alert or conditional formatting, but I'm not sure how to accomplish this. For the columns impacted they are all dropdown single select columns. I want some sort of alert, could be as simple as highlighting the row, if a value in "Faculty on PTO" appears in any other column for that specific row. For instance, if Dr. Smith is on PTO for that day, he/she should not be listed as on call for any of the options.



Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    So you need a helper column called "Duplicates". Use COUNTIF to see how many instances of that value are on the row:

    =COUNTIF([General Attending 7a-7a]@row:[Pediatric Attending]@row, =[Faculty on PTO]@row)

    Make this a column formula.

    For rows without the Faculty on PTO value in the other columns, this will equal 0. Set your alert or conditional formatting for when Duplicates column > 0.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thank you for your help on this issue. I tried it, and other than needing to expand my formula to include all columns, it worked! However, I'm wondering, will this work if my columns are using dropdown selections?

  • Sorry, but I wanted to add more information. My "Faculty on PTO" column will have multiple names in the cells, but all of my cells to the right of that will only have one name. As an example, Dr. Smith and Dr. Jones may both appear in "Faculty on PTO" for any given day, say the 1st; and I need the formula to detect if either of them is in any cells to the right on the 1st.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Amber Angon

    Thank you for clarifying that "Faculty on PTO" will be multi-select. This actually means you'll want to swap around what your formula is looking at.

    Instead of searching for the "Faculty on PTO" value in the rest of your row, you'll want to search the "Faculty on PTO" cell to see if it HAS the value from any of the other cells.

    Ex:

    =IF(HAS([Faculty on PTO]@row, [General Attending 7a-7a]@row), "Duplicate")

    Or

    =IF(HAS([Faculty on PTO]@row, [General Attending 7a-7a]@row), "Duplicate In General Attending")

    You can use multiple IF statements to return all of the possible column names in your helper column, then use this helper column as the source for your Conditional Formatting rule.

    =IF(HAS([Faculty on PTO]@row, [General Attending 7a-7a]@row), "Duplicate In General Attending" + CHAR(10)) + IF(HAS([Faculty on PTO]@row, [Spine Attending 7a-7a]@row), "Duplicate In Spine Attending" + CHAR(10))

    The CHAR(10) creates a line break, so you could input this formula into a multi-select column to return multiple values.

    Let me know if this makes sense and will work for you!

    Cheers,

    Genevieve

  • Hi,

    This formula worked great for my first 4 columns, but would not work for my remaining 6 columns. I even tried each individual formula separately and received the same error for the final 6 columns "Incorrect Argument Set". Here is the formula I used, I had them all together, but thought this would be easier to look at as individual:

    IF(HAS([Faculty on PTO]@row, [General Attending]@row), "Duplicate In General Attending" + CHAR(10))

    + IF(HAS([Faculty on PTO]@row, [Spine Attending]@row), "Duplicate In Spine Attending" + CHAR(10))

    + IF(HAS([Faculty on PTO]@row, [Pediatric Attending]@row), "Duplicate In Pediatric Attending" + CHAR(10))

    + IF(HAS([Faculty on PTO]@row, [Lovelace Attending]@row), "Duplicate In Lovelace Attending" + CHAR(10))

    + IF(HAS([Faculty on PTO]@row, [Back Up Trauma Call UNM]@row, "Duplicate In Back Up Trauma Call UNM" + CHAR(10))

    + IF(HAS([Faculty on PTO]@row, [Neurosurgery Aneurysm Attending]@row, "Duplicate In Neurosurgery Aneurysm Attending" + CHAR(10))

    +IF(HAS([Faculty on PTO]@row, [Endovascular/Stroke Thrombectomy Attending]@row, "Duplicate In Endovascular/Stroke Thrombectomy Attending" + CHAR(10))

    +IF(HAS([Faculty on PTO]@row, [Tumor Call/ACCESS]@row, "Duplicate In Tumor Call/ACCESS" + CHAR(10))

    +IF(HAS([Faculty on PTO]@row, [Neuro ICU Attending-DAYS]@row, "Duplicate In Neuro ICU Attending-DAYS" + CHAR(10))

    +IF(HAS([Faculty on PTO]@row, [Neuro ICU-NIGHTS]@row, "Duplicate In Neuro ICU-NIGHTS" + CHAR(10))

  • Hi again,

    Nevermind! Realized I forgot a close parentheses for the last 6 formulas. oops. Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Amber, thanks for following up! I'm glad it works for you now. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!