Pairing non-teammate competitors

[Woodwalker]

Need help please, I like to match-up competitors randomly where they can't face their own teammates and they can only face the opponent once. Data is taken from a table
that's arrange like this (Column A: Competitor, Column B: Team). In another table is the output (Column D: Competitor 1, Column E: Team, Column F: Competitor 2, Column G: Team), also there's no duplicates of any competitor's name in Columns D and F. Any help would appreciated. Thanks. Sorry for bad english.

 

[Bosquedeguate]

something like this?

Team Table (used for entry lookup)

GetLastRow.xlsm
	J
90	Teams
91
92	Snow White
93	Donald Duck Nephews
Sheet1

Competitor & Team Table

GetLastRow.xlsm
	A	B
91
92	Doc	Snow White
93	Sleepy	Snow White
94	Grumpy	Snow White
95	Happy	Snow White
96	Dopey	Snow White
97	Sneezy	Snow White
98	Bashful	Snow White
99	Huey	DD Nephews
100	Dewy	DD Nephews
101	Louie	DD Nephews
102	Manny	Pep Boys
103	Moe	Pep Boys
104	Jack	Pep Boys
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B91:B98	List	=TeamList

Event and Competitor Table

GetLastRow.xlsm
	D	E	F	G	H
90	Competitor 1	Team	Competitor 2	Team	Status
91	Sleepy	Snow White	Dewy	DD Nephews	OK
92	Huey	DD Nephews			OK
93			Huey	DD Nephews	OK
94					OK
95					OK
96					OK
97					OK
98					OK
99					OK
100					OK
101
102
103
104
Sheet1

Cell Formulas
Range	Formula
E91:E100,G91:G100	G91	=IF(LEN(F91)>0,VLOOKUP(F91,Competitors,2,FALSE),"")
H91:H92	H91	=IF(E91=G91,"Team Mates",IF(D91=F91,"Duplicate Competitor","OK"))
H93:H100	H93	=IF(AND(E93=G93,LEN(G93)>0),"Team Mates",IF(AND(LEN(F93)>0,D93=F93),"Duplicate Competitor","OK"))

Named Ranges
Name	Refers To	Cells
CompetitorList	=Sheet1!$A$92:$A$104	E91:E100, G91:G100
Competitors	=Sheet1!$A$91:$B$104	E91:E100, G91:G100
TeamList	=Sheet1!$D$91:$D$93	H91, E91

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D91:D100,F91:F100	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D91:D100	List	=CompetitorList
F91:F100	List	=CompetitorList

 

[Bosquedeguate]

When Team Mates are the same the Status changes:

Moe	Pep Boys	Jack	Pep Boys	Team Mates
Jack	Pep Boys	Huey	DD Nephews	OK
Manny	Pep Boys	Huey	DD Nephews	OK
Happy	Snow White	Bashful	Snow White	Team Mates

Please note: I removed the duplicate highlighting that was in the previous example.

 

[Woodwalker]

Thanks for your time and effort. Your second table, the competitor & team table is correct, because that's what I have in my sheet, but I still need a formula for the randomization of the column F (and possibly D column). I forgot to write that there are 80 competitors divided in 11 groups of 6 with an additional two groups with 7 members (11 x 6 = 66) + (2 x 7 = 14)= 80. I don't need a formula for columns E and G (Teams Output) because I will just use index match with that. Our objective is not to determine if the output table has duplicates or not but to have an output of randomly matched non-teammate competitors without duplicates within the columns D and F(Competitors' Output). I hope you understand my reply.

 

[Bosquedeguate]

Are there the same number of team members in each team?

 

[Woodwalker]

Are there the same number of team members in each team?

like I said, there are a total of 13 teams, 11 team each have 6 members while the other 2 teams each have 7 for a total of 80 competitors.

 

[Bosquedeguate]

Sorry you referred to groups and teams. I didn’t understand the were synonymous.

 

[Bosquedeguate]

Are these 80 individuals randomly assigned to a team by you? Or do these competitors come as a team?

 

[Woodwalker]

Sorry if you got confused with the teams and groups. I assigned each individuals to a team myself not in random. I'm actually trying to do a tournament with a format similar to Yugioh Battle City Preliminaries if you're familiar with this anime, it has a battle-royale format where they roam the whole city to seek opponents. Pretty exciting isn't it?

 

[Bosquedeguate]

Hi ... just an update. I have not forgotten about you. I have been working on code to be able to do a Union, Intersection, and Complement (A - B) operation of two sets of data.
Eg - your Name/Team table and the competitor pairing. Being able to do this operation repeatedly will be the key to arriving at solution for your Competitor pairing.