Forum Discussion
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 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:
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.
7 Replies
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.
- Hello Hans. Apologies for the late acknowledgement. I couldn't visualise this solution, but it works perfectly - thank you for your kind assistance.
Mike
