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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Course[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Bookkeeping Basics[/TD]
[TD]richard@aaa.com[/TD]
[/TR]
[TR]
[TD]Bookkeeping Basics[/TD]
[TD]peter@bbb.com[/TD]
[/TR]
[TR]
[TD]Bookkeeping Basics[/TD]
[TD]sally@ccc.com[/TD]
[/TR]
[TR]
[TD]Management for Team Leaders[/TD]
[TD]richard@aaa.com[/TD]
[/TR]
[TR]
[TD]Management for Team Leaders[/TD]
[TD]debby@ddd.com[/TD]
[/TR]
[TR]
[TD]Social Media Update[/TD]
[TD]richard@aaa.com[/TD]
[/TR]
[TR]
[TD]Social Media Update[/TD]
[TD]sally@ccc.com[/TD]
[/TR]
[TR]
[TD]Social Media Update[/TD]
[TD]lisa@eee.com[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]CourseName[/TD]
[TD]Accounting[/TD]
[TD]Management[/TD]
[TD]Marketing[/TD]
[/TR]
[TR]
[TD]Bookkeeping Basics[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Management for Team Leaders[/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Social Media Update[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[/TR]
</tbody>[/TABLE]

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

DELEGATES

[TABLE="width: 500"]
<tbody>[TR]
[TD]Email[/TD]
[TD]Accounting[/TD]
[TD]Management[/TD]
[TD]Marketing[/TD]
[/TR]
[TR]
[TD]richard@aaa.com[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD]peter@bbb.com[/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sally@ccc.com[/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD]debby@ddd.com[/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lisa@eee.com[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[/TR]
</tbody>[/TABLE]


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.