Any help is appreciated

[NotSoProficientInExcel]

In one table (#1), there are two columns with roles, and we want to make sure the combination of the two roles in the same row are assigned to one individual:

Role 1	Role 2
Dishes	Lawn

In another table (#2), there are two columns, one with a name and another with all Roles assigned to that person:

Name	Roles
John Smith	Lawn, Windows, Vacuum, Dishes

In the last table (3#), there are two columns, one with the individual's name and another with an assigned Role per row:

Name	Role
John Smith	Lawn
John Smith	Windows
John Smith	Vacuum
John Smith	Dishes

The goal here is to flag/locate the assignment combination of Dishes and Lawn from Table #1 in both 2 and 3. Is there a formula or other way in both table 2 and 3 to show that John Smith was assigned the combination of Dishes and Lawn, no matter the sequence the roles are in? Any help is appreciated!

 

[dreid1011]

Where in table 1 is the name assigned? And are you just looking for cells in tables 2 and 3 to be highlighted? The names, and roles or just the names or just the roles?

 

[NotSoProficientInExcel]

Where in table 1 is the name assigned? And are you just looking for cells in tables 2 and 3 to be highlighted? The names, and roles or just the names or just the roles?

Table 1 includes the role combinations that need to be located or identified in Tables 2 and 3. There are no names associated with it. Tables 2 and 3 are two different formats of the same data set, and we are trying to locate everyone in both tables with the role combination(s), in this case Dishes & Lawn, listed in Table 1. Given the formats of 2 and 3, is this possible?

 

[dreid1011]

Then maybe something like this:

Book1
	A	B	C
1	Table 1
2	Role 1	Role 2
3	Dishes	Lawn
4
5	Table 2
6	Name	Roles
7	John Smith	Lawn, Windows, Vacuum, Dishes	TRUE
8	Jane Smith	Windows, Dishes, Trash	FALSE
9	Alice Jones	Vacuum, Dishes	FALSE
10	Jason Troy	Lawn, Trash, Sidewalks	FALSE
11	Melissa Newton	Windows, Dishes, Lawn	TRUE
12
13
14	Table 3
15	Name	Role
16	John Smith	Lawn	TRUE
17	John Smith	Windows	FALSE
18	John Smith	Vacuum	FALSE
19	John Smith	Dishes	TRUE
20	Jane Smith	Windows	FALSE
21	Jane Smith	Dishes	FALSE
22	Jane Smith	Trash	FALSE
23	Alice Jones	Vacuum	FALSE
24	Alice Jones	Dishes	FALSE
25	Jason Troy	Lawn	FALSE
26	Jason Troy	Trash	FALSE
27	Jason Troy	Sidewalks	FALSE
28	Melissa Newton	Windows	FALSE
29	Melissa Newton	Dishes	TRUE
30	Melissa Newton	Lawn	TRUE
Sheet3

Cell Formulas
Range	Formula
C7:C11	C7	=AND(ISNUMBER(SEARCH($A$3,B7)),ISNUMBER(SEARCH($B$3,B7)))
C16:C30	C16	=AND(COUNTIFS($A$16:$A$30,$A16,$B$16:$B$30,$A$3)+COUNTIFS($A$16:$A$30,$A16,$B$16:$B$30,$B$3)=2,OR($B16=$A$3,$B16=$B$3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A16:B30	Expression	=AND(COUNTIFS($A$16:$A$30,$A16,$B$16:$B$30,$A$3)+COUNTIFS($A$16:$A$30,$A16,$B$16:$B$30,$B$3)=2,OR($B16=$A$3,$B16=$B$3))	text	NO
A7:B11	Expression	=AND(ISNUMBER(SEARCH($A$3,$B7)),ISNUMBER(SEARCH($B$3,$B7)))	text	NO

Col C is just the same CF rules but on the sheet to show example of which rows to highlight. I must note, these formulas are specifically set up for combinations of only 2 roles. If you need something more dynamic, I will have to revisit.

 

[NotSoProficientInExcel]

Thanks so much! This worked

 

[dreid1011]

Thanks so much! This worked

You're welcome, and happy to help.