Random Pairing with 2 variables

[hrts4him]

I am trying to create a random match/pair of a person on one team (Team Member) to a person on a different team (Doesn't have the same Team Lead). They cannot meet with themselves and they cannot repeat.

Team Member	Team Lead
Sam	Mary
Mary	Mark
Jane	Mark
John	Sam
Sue	John
David	Fran

In this example: Mary and Jane will never meet because they have the same Team Lead Mark. If they are on both list, team member and team lead they will never meet IE: Mary will never meet with herself. If they meet once they will not meet again in future interactions.

I can understand the =rand() would work if I didn’t have to worry about that second variable of a team lead.

Thanks in advance for the assistance!

 

[bines53]

Hi,
There are 33 possibilities.
=SUMPRODUCT(COUNTIF(A2:A7,"<>"&B2:B7))

David

 

[hrts4him]

Hi,
There are 33 possibilities.
=SUMPRODUCT(COUNTIF(A2:A7,"<>"&B2:B7))

David

Thank you for your response. So, how would I show the 33 pairs.

 

[bines53]

Hi,

What version of Excel do you have?

 

[hrts4him]

Hi,

What version of Excel do you have?

Office 16

 

[bines53]

Hi ,

{=(A2:A7<>TRANSPOSE(B2:B7))*1}

I uploaded the solution in the file, and also the solution through a matrix, copy it into the file. And you will see the pairs you are looking for, the formula of the matrix is in cse.

 

[hrts4him]

I need something like this but for it to autopopulate/generate the fields in month 1, month 2, etc. Caution is they can only meet once (no repeats) and if they have the same team lead they can not meet. So Mary and Jane can not meet. Mary can not meet with herself as well seeing that she is a team memeber and a Team Lead.

			Month 1		Month 2
Team Member	Team Lead		Person 1	Person 2	Person 1	Person 2
Sam	Mary		Sue	Mark	Sam	David
Mary	Mark		Sam	Fran	Mary	Sue
Jane	Mark		John	Jane	Fran	Mark
John	Sam		David	Mary	Jane	John
Sue	John
David	Fran

 

[hrts4him]

Hi ,

{=(A2:A7<>TRANSPOSE(B2:B7))*1}

I uploaded the solution in the file, and also the solution through a matrix, copy it into the file. And you will see the pairs you are looking for, the formula of the matrix is in cse.

See my new reply sorry should have replied here but didn't

 

[bines53]

Hi,

Open a new thread, upload a file, and give two examples in the file, which solution you are looking for.

David