Cross-check multiple instances for a match

[Richard JIBS]

Hello,

I'm looking to match delegates at courses to interest columns based on the topic of the course.

The original data looks like this:

DATA EXPORT

Course	Email
Bookkeeping Basics	richard@aaa.com
Bookkeeping Basics	peter@bbb.com
Bookkeeping Basics	sally@ccc.com
Management for Team Leaders	richard@aaa.com
Management for Team Leaders	debby@ddd.com
Social Media Update	richard@aaa.com
Social Media Update	sally@ccc.com
Social Media Update	lisa@eee.com

<tbody>
</tbody>

I have pulled out all the courses into a list with no duplicates into a second worksheet - and assigned interest categories to each course:

COURSES

CourseName	Accounting	Management	Marketing
Bookkeeping Basics	x
Management for Team Leaders		x
Social Media Update			x

<tbody>
</tbody>

I have pulled out all unique delegate email addresses into a third worksheet:

DELEGATES

Email	Accounting	Management	Marketing
richard@aaa.com	x	x	x
peter@bbb.com	x
sally@ccc.com	x		x
debby@ddd.com		x
lisa@eee.com			x

<tbody>
</tbody>

What I would like is a way of filling out the "x"s in the DELEGATES worksheet based on the DATA EXPORT and COURSES worksheets.

So with richard@aaa.com in the Accounting column, the formula would check all instances of richard@aaa.com in DATA EXPORT – and then if richard@aaa.com has attended any course with an Accounting interest it would add an x to the cell.

Any help is much appreciated!

Thanks,

Richard

 

[SpillerBD]

I would use a Pivot Table Report with conditional formatting.
The Pivot Table would show Count Of... 1. Conditional Format that any value of >= 1 would be a filled color and Text color to highlight appropriately.