help tying two values to locate duplicates

Hi - I have a sheet with PARF #s and Position #s -- I would like to flag any row that has the same PARF# and same Pos# in the column to locate a duplicate employee --

ie., PARF# Position # Flag

12345 789

9998 789

12345 789 X (duplicate)

I've tired the Count IF with And but it does not work -- I feel like it should be simple... any help would be appreciated.

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Instead of COUNTIF with AND, use COUNTIFS (this allows you to look at multiple pieces of logic).

    =COUNTIFS([PARF#]:[PARF#], [PARF#]@row, [Position#]:[Position#], [Position#]@row)


    Then add an IF so that if the value of the COUNTIFS is greater than 1 it returns X.

    =IF(COUNTIFS([PARF#]:[PARF#], [PARF#]@row, [Position#]:[Position#], [Position#]@row) > 1, "X")


    This will flag both duplicate rows, is that acceptable?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!