After you have dealt with what is in previous post (and not before !!)
To catch the 2nd name
Add a 2nd rule and format it differently so that you can
- identify any issues during testing
- and know (when live) that it is the 2nd name that is being rostered while on leave
The formula is
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2))-1)&"*")>0
------------------------------------------------------------------------------------------------------
A couple of foreseeable issues:
------------------------------------------------------------------------------------------------------
Now ...
For the second name to be matched the value in the cell must be entered like this
Name1
CHAR((10)
Time
CHAR((10)
Name2
CHAR(10)
So ...
The problem that gives is that a cell that looks like this will not work
The first name is picked up by the original rule, but the 2nd name is in the wrong place
Sharylanda
(iPharm Upgrade)
08:30 - 12:00
Karen
12:00-17:00
To deal with that eventuality (there are no examples in your data) but presumably there is no logical reason why it could never happen
The formula becomes
=OR(COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3))-1)&"*")>0,COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2))-1)&"*")>0)
Simplified all the formula does is this
=OR( does Line 4 match, does line 3 match)
This is what I would do: ...
Another way to deal with it would be to leave the 2nd rule as
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2))-1)&"*")>0
and add the other rule separately but use the same format as 2nd rule
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3))-1)&"*")>0
How many names could there be?
I do not know if you sometimes have 3 or even 4 names in the same cell, but if that is the case you could (in theory) keep adding extra rules to make sure each one is matched
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",5)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4))-1)&"*")>0
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",5)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",6)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",5))-1)&"*")>0
etc
If you think that would be valuable, then DO NOT CONDITIONALLY FORMAT WHOLE COLUMNS but select a "large enough" range of cells - otherwise you will end up with a VERY slow performing workbook
The quality of input data
Everything I have provided only works if there is good control exercised over the input of data
- sloppy input will result in names not matching
Spaces
I would like to amend all the formulas I have provided to deal with someone entering a space AFTER a name and before CHAR(10)
It occurs to me that
- it will not result in a match as things stand
- it will not be obvious because you cannot see the space when looking in the cell
Q What happens when there are 2 people with the same name ?
Q Do you use Sam1, Sam2 ?
Q Are there ever spaces in any names for any reason ?
Unless you standardise on names without a space I cannot deal with that potential typo
- I will need your thoughts on this in due course