Jan 24 2022 08:37 AM
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:
Jan 24 2022 11:30 AM
SolutionSelect 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.
Jan 25 2022 02:39 AM
Jan 27 2022 08:14 AM
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?
Jan 27 2022 10:59 AM
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.
Jan 28 2022 01:23 AM
@Hans Vogelaar You're a wonderful human being! Thank you ever so much for your help. It's greatly appreciated.
Jan 28 2022 02:27 AM
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
Jan 28 2022 04:35 AM
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"))
Jan 24 2022 11:30 AM
SolutionSelect 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.