How to highlight duplicate values but each value has two possible variations

[cthorne1]

So I have a list of team member names and usernames

Then on a different page, I have the different roles the team members can do and that is how we staff them. I want to have duplicate values highlighted weather it's the team members name or their username to prevent someone from being staffed twice. So in the picture bellow I want jdoe2 (B2) and John Doe (B4) to be highlighted because it's the same person being assigned two different functions.

Any idea how to make that happen?

 

[Cubist]

I don't believe it's doable via conditional formatting. However, it is possible via
(1) VBA
(2) Another option is to have another column to indicate duplication. See below.
Let us know which option you want to pursue.

Book3
	A	B	C	D	E	F
1	User	Name		Role	Team
2	jdoe2	John Doe		Cleaner	jdoe2	Duplicate
3	jsmith9	Jane Smith		Picker	Jane Smith
4	ltimmy1	Little Timmy		Packer	John Doe	Duplicate
5				Runner	ltimmy1
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=LET( name,BYROW(E2:E5,LAMBDA(name,IF(NOT(ISNUMBER(SEARCH(" ",name))),VLOOKUP(name,A2:B4,2),name))), IF(BYROW(name,LAMBDA(br,ROWS(FILTER(name,name=br))))>1,"Duplicate","") )
Dynamic array formulas.

 

[cthorne1]

I don't believe it's doable via conditional formatting. However, it is possible via
(1) VBA
(2) Another option is to have another column to indicate duplication. See below.
Let us know which option you want to pursue.

Book3
	A	B	C	D	E	F
1	User	Name		Role	Team
2	jdoe2	John Doe		Cleaner	jdoe2	Duplicate
3	jsmith9	Jane Smith		Picker	Jane Smith
4	ltimmy1	Little Timmy		Packer	John Doe	Duplicate
5				Runner	ltimmy1
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=LET( name,BYROW(E2:E5,LAMBDA(name,IF(NOT(ISNUMBER(SEARCH(" ",name))),VLOOKUP(name,A2:B4,2),name))), IF(BYROW(name,LAMBDA(br,ROWS(FILTER(name,name=br))))>1,"Duplicate","") )
Dynamic array formulas.

I think option 2 sounds more in applicable for what I have currently. Thank you!!

 

[cthorne1]

I don't believe it's doable via conditional formatting. However, it is possible via
(1) VBA
(2) Another option is to have another column to indicate duplication. See below.
Let us know which option you want to pursue.

Book3
	A	B	C	D	E	F
1	User	Name		Role	Team
2	jdoe2	John Doe		Cleaner	jdoe2	Duplicate
3	jsmith9	Jane Smith		Picker	Jane Smith
4	ltimmy1	Little Timmy		Packer	John Doe	Duplicate
5				Runner	ltimmy1
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=LET( name,BYROW(E2:E5,LAMBDA(name,IF(NOT(ISNUMBER(SEARCH(" ",name))),VLOOKUP(name,A2:B4,2),name))), IF(BYROW(name,LAMBDA(br,ROWS(FILTER(name,name=br))))>1,"Duplicate","") )
Dynamic array formulas.

I have realized that option 2 will not work because I have multiple columns of roles for people to do, so it looks a bit more like this:

Could you walk me through the VBA option instead?

 

[Cubist]

When you stack them sideways like that it's more challenging because not everything is in the same column. What is the actual sheet laid out? How are the group locate relative to the others?

 

[cthorne1]

When you stack them sideways like that it's more challenging because not everything is in the same column. What is the actual sheet laid out? How are the group locate relative to the others?

So... This is actually how things are actually laid out, each of these is on a different page. Each of the blank cells can have names in them.

 

[Cubist]

Can you manually fill out one level? This is entirely different from what you posted before. Now you have a numeric column in between?
Please don't simplify the problem.