How to alert regarding a duplicate value within a row?

Amber Angon

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.

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.

Amber Angon

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?

Amber Angon

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.

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

Amber Angon

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))

Amber Angon

Hi again,

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

Genevieve P.

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