Random Teams with Excel formula

[Bobntn]

Trying to use a list of 2 man golf teams then randomize four man teams (foursomes) without repeating a name nor using their two man team partner on the same foursome.
The list of 2 man teams can decrease or increase.
Can this be done with an Excel formula without using vba ?

 

[Sandie1712]

pl share some data or come up with examples

Thanks

 

[Bobntn]

pl share some data or come up with examples

Thanks

I tried to post a jpg but it only asks for a url.

Col B Col C Foursome 1 Foursome 2 Foursome 3 Foursome 4 Foursome 5
Ron Bob
John Tony
Brenda Vicky
Steve Wayne
Bill Jason
and so on

Ron is Bob's 2 man team partner, John is Tony's, Brenda is Vicky's etc.

I need to randomly create foursome teams from the two lists but not select any player more than once and not be in the same foursome of his or her 2 man partner. In other words, Ron would only be selected for one foursome and could play with any other player in the lists except his partner Bob.
Also the 2 man team lists in Columns B and C can grow or get smaller plus the number of foursome would also increase or decrease based on the number in the 2 man teams list.

Does this help ?

 

[AhoyNC]

See the attached link for golf pairing. Not Excel, but easier than reinventing something in Excel.
Free round robin tournament schedule / pairings generator – GolfSoftware.com

 

[Bobntn]

See the attached link for golf pairing. Not Excel, but easier than reinventing something in Excel.
Free round robin tournament schedule / pairings generator – GolfSoftware.com

I saw that but I am looking to incorporate it into an extensive XL workbook.
Thanks anyway for your time.

 

[Bobntn]

Just as information, now that my tournament is coming up and I am now trying to do this again, I found that using the formula
=INDEX(range of names,MRAND())
placed in the number of cells I want returned then doing a Ctrl+Shift+Enter places the formula in all cells will randomize as I want EXCEPT
will not restrict the 2 man team's partner from being possibly included.
I will have to work on that.
This method, of course, does not require VBA.