Forum Discussion
Hide other duplicates using cond formatting + IF function based on x in adjacent cell
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.
Hi again,
I've tried to adapt this formula to hide the other names only after the one name is selected. Currently all the duplicate names are hidden until one is selected, but the problem is that you can't see the names to determine which ones to hide!
I tried formatting it differently so that the names are greyed out, but it's still too confusing when there's lots of columns to choose names from.
Is there a way to make the other names white text (effectively hidden), when one name is selected?
Use this formula:
=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:$F5,"X"))See the attached version.
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
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"))
HansVogelaar You're a wonderful human being! Thank you ever so much for your help. It's greatly appreciated.