Where did the traveling salesman get the flu?

[Tommy2Tables]

I want to find what person is most likely to have infected my salesman.
I have two tables, one of sick salesmen and their customers and the well salesmen and their customers.
The customers overlap.
I want to find the most common customers that might have made them sick by subtracting out the customers that the sick and well both saw.

I can find out which are the most common customers between the salesmen with an Advanced Filter and COUNTIF, like this procedure 1) Select the entire column B
2) Open the Data...Filter...Advanced Filter menu item
3) Check the boxes for "Unique records only" and "Filter the list, in place"
4) Click OK. Excel will now collapse the list into just the unique records. All other rows are hidden.
5) Enter this formula in cell C2: =COUNTIF($B$2:$F$50,B2)
6) Copy this formula down to the end of your list
then descending sort This procedure is described here:
Counting frequency and ranking

But I also want it to remove the customers in common with the well salesmen who did not get them sick.

SICK SALESMEN	CUSTOMERS
Roger Oneil	Ines Lobaugh
Roger Oneil	Ramona Borges
Roger Oneil	James Wheat
Roger Oneil	Joey Flory
Roger Oneil	Ross Beckemeyer
Roger Oneil	Ines Lobaugh
Pedro Conway	Ines Lobaugh
Pedro Conway	Brian Matias
Pedro Conway	Twila Leimbach
Pedro Conway	Andy Kysar
Pedro Conway	Ramona Borges
Pedro Conway	Belinda Roby
Eric McKinnis	Ines Lobaugh
Eric McKinnis	Ramona Borges
Eric McKinnis	Michael Amato
Eric McKinnis	Ines Lobaugh
Eric McKinnis	Belinda Roby
Gwen Andrew	Ines Lobaugh
Gwen Andrew	Ramona Borges
Gwen Andrew	Bridgett Althaus
Gwen Andrew	Belinda Roby
Lawrence Curran	Ines Lobaugh
Lawrence Curran	Ramona Borges
Lawrence Curran	Bridgett Althaus
Lawrence Curran	Concetta Tasso
Lawrence Curran	Belinda Roby
Howard Acker	Ines Lobaugh
Howard Acker	Ramona Borges
Howard Acker	Stuart Phaup
Howard Acker	Willard Bolan
Howard Acker	John Gottard
WELL SALESMEN	CUSTOMERS
Mara Maltz	Ines Lobaugh
Mara Maltz	Bridgett Althaus
Mara Maltz	Lillie Carpenter
Mara Maltz	Cora Mcnamara
Jimmy Lowder	Ines Lobaugh
Jimmy Lowder	Vivian Bogart
Jimmy Lowder	Victoria Madison

<colgroup><col span="2"></colgroup><tbody>
</tbody>

In this example, everyone well and sick, visits Ines Lobaugh, so even though she is very common, she should be subtracted from the results.
Thank you.
Tommy2Tables

 

[shawnhet]

There are probably many different ways to do this but here's mine.

I put your sick persons table in A2:A32 and your well salesmen table in G2:H2

Then in C2 I posted and copied down

=if(IF(ISERROR(VLOOKUP(B2,$H$2:$H$8,1,FALSE)),1,0)*COUNTIF($B$2:$B$32,B2)=6,B2,"")
The six is for the fact that there are six sick salesmen.

Cheers,

 

[Marcelo Branco]

Hi and welcome to MrExcel forum

To find what person is most likely to have infected the salesmen maybe something like this

Assuming table1 in A1:B32 and table2 in D1:E8

A B C D E F G H I J

SICK SALESMEN	CUSTOMERS		WELL SALESMEN	CUSTOMERS		CUSTOMERS		Criteria	Max
Roger Oneil	Ines Lobaugh		Mara Maltz	Ines Lobaugh		Ramona Borges		FALSO	6
Roger Oneil	Ramona Borges		Mara Maltz	Bridgett Althaus
Roger Oneil	James Wheat		Mara Maltz	Lillie Carpenter
Roger Oneil	Joey Flory		Mara Maltz	Cora Mcnamara
Roger Oneil	Ross Beckemeyer		Jimmy Lowder	Ines Lobaugh
Roger Oneil	Ines Lobaugh		Jimmy Lowder	Vivian Bogart
Pedro Conway	Ines Lobaugh		Jimmy Lowder	Victoria Madison
Pedro Conway	Brian Matias
Pedro Conway	Twila Leimbach
Pedro Conway	Andy Kysar
Pedro Conway	Ramona Borges
Pedro Conway	Belinda Roby
Eric McKinnis	Ines Lobaugh
Eric McKinnis	Ramona Borges
Eric McKinnis	Michael Amato
Eric McKinnis	Ines Lobaugh
Eric McKinnis	Belinda Roby
Gwen Andrew	Ines Lobaugh
Gwen Andrew	Ramona Borges
Gwen Andrew	Bridgett Althaus
Gwen Andrew	Belinda Roby
Lawrence Curran	Ines Lobaugh
Lawrence Curran	Ramona Borges
Lawrence Curran	Bridgett Althaus
Lawrence Curran	Concetta Tasso
Lawrence Curran	Belinda Roby
Howard Acker	Ines Lobaugh
Howard Acker	Ramona Borges
Howard Acker	Stuart Phaup
Howard Acker	Willard Bolan
Howard Acker	John Gottard

<tbody>
</tbody>

Enter the Headers CUSTOMERS, Criteria and Max in respectively G1, I1, J1

In J2 enter this array formula
=MAX(IF(ISNA(MATCH($B$2:B$32,E$2:E$8,0)),COUNTIF($B$2:$B$32,$B$2:$B$32)))

must be confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

in I2 (under Criteria) enter this formula
=AND(ISNA(MATCH(B2,$E$2:$E$8,0)),COUNTIF($B$2:$B$32,B2)=$J$2)

SElect A1:B32 and go to Data> Advanced Filter

pick Copy to another location
List range: A1:B32
Criteria Range: I1:I2
Copy to: G1
check Unique records only

Ok

M.

 

[Tommy2Tables]

I did not respond because this solution and the other one blew my mind. I am going to try to take on smaller chunks of this at one time. Thank you. T2T