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

[mlee147]

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 trying to develop a tool which I can use to select a name in any one list (by putting an 'x' in the cell next to it), but hide all other instances of it in the other lists.

There may be more than one instance of a duplicated name, so this functionality needs to work for each different duplicated name.

I have tried to figure this out myself, but I can only figure the constituent parts.

The =COUNTIF($A$1:$A1, A1)>1 function highlights duplicates. I can use conditional formatting to make the text white, effectively hiding it. And I can use the IF function to recognise an adjacent cell, and perform an action based on it. I just can't figure out how to pull all this together in the example below:

[#2 · Accepted solution]

@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.

[#3]

Hello Hans. Apologies for the late acknowledgement. I couldn't visualise this solution, but it works perfectly - thank you for your kind assistance.
Mike

[#4]

@Hans Vogelaar 

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?

[#5]

@mlee147 

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.

[#6]

@Hans Vogelaar You're a wonderful human being! Thank you ever so much for your help. It's greatly appreciated.

[#7]

@Hans Vogelaar 

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

[#8]

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