Forum Discussion

mlee147's avatar
mlee147
Copper Contributor
Jan 24, 2022
Solved

Hide other duplicates using cond formatting + IF function based on x in adjacent cell

Hi All, I'm working on a personnel deployment tool, which gives me a list of names under specific work areas. However, some operatives are qualified to work in more than one area. Therefore I'm tr...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 24, 2022

    mlee147 

    Select E2:E5, then hold down Ctrl and select C2:C5, and finally, with Ctrl still held down, select A2:A5. You can then release Ctrl.

    A2 should now be the active cell in the selection.

    Create a conditional formatting rule of type Formula with formula

    =AND(B2="",COUNTIF($A$2:$E$5,A2)>1)

    See the attached sample workbook. I used the custom number format ;;; (three semicolons) to hide the duplicate entries.

mlee147's avatar
mlee147
Copper Contributor
Jan 28, 2022

HansVogelaar 

Apologies, on further checking, this solution only seems to work on the 'Mike's' and not the 'Simon's'?

 

Also, if I add another 'Duplicate Values' conditioning rule, I can use this to highlight the duplicate names. I think this is ok and doesn't appear to stop your original formula from functioning. See attached.

 

Thanks again, and for your patience,

 

Mike

HansVogelaar's avatar
HansVogelaar
MVP
Jan 28, 2022

mlee147 

My apologies - there was a typo. The last $F5 in the formula should be $F$5:

 

=AND(B2="",COUNTIF($A$2:$E$5,A2)>1,COUNTIFS($A$2:$A$5,A2,$B$2:$B$5,"X")+COUNTIFS($C$2:$C$5,A2,$D$2:$D$5,"X")+COUNTIFS($E$2:$E$5,A2,$F$2:$F$5,"X"))

Resources