3 Unique Random Lists

[jeffatworkorhome]

How would you create 3 unique random lists, where each individual list is different than the other. I’m trying to create a random pairing for golf for 3 rounds. The rows must be unique and the columns must be Player-1 thru Player-8. I atttempted Randarray, but cant filter conditions for duplicates. See Example below for clarification:

[Limor_OP]

Hi,
what is the formula you are currently using?

[jeffatworkorhome]

I'm not currently using anything as I cannot get to work accurately.

[menem]

If you allow me to use helper columns.
please try

Please Login or Register  to view this content.

Regards.

[davsth]

I think there may be another constraint. Each player can only appear once in each column. I guess Jeff can confirm

[jeffatworkorhome]

Yes that is correct. Each player can only show up once in each column.

[jeffatworkorhome]

To clarify, for example: Match-1 if Jeff plays Steve, then Steve must play Jeff. To make it more difficult, Match-2 cannot have the same two people as Match-1.

[JohnTopley]

Is this what is required? You will need VBA to do this as a cell cannot both be a value and formula which is needed to solve this.

This was a simple manual exercise.

[davsth]

I think this will be a vba solution solved by repetition untill you criteria are satisified

the attached is inelegant, but as your number of people is small is workable

It matches the people so rank 1 and 2 go together, 3 and 4 go together etc etc

However to be an acceptable solution the names in the row can not appear more than once

click the button to generate a solution

Please Login or Register  to view this content.

there will be amore elegant solution out there I am sure this is rough and ready

[jeffatworkorhome]

Thanks davsth that works!

[jeffatworkorhome]

davsth - although the loop doesn't appear to be working. Do I have to execute the macro until C23=0?

[Metoo7]

Select range D3:D10, enter the below formula, press Ctrl+Shift+Enter:

Please Login or Register  to view this content.

Attachment 745648

OR

Please Login or Register  to view this content.

asdf.png

Final version...

[jeffatworkorhome]

Metoo7 - thanks for the efforts, the only drawback is the "Players" cannot play against themselves, therefore there would have to be a condition to validate that. If I assigned Players A-H as players 1-8, there will certainly be duplicates.

[Metoo7]

At first, I have misunderstood your question. I just realised this and update the formula and edit the thread in above floor #12.

You can find my example in worksheet "3 random players (2)"

formula is

Please Login or Register  to view this content.

[Metoo7]

a lookup function will do.

Please Login or Register  to view this content.

[jeffatworkorhome]

Metoo7 - in your spreadsheet for Match-1, if Player Jack plays Cloud, then Cloud must play Jack... this is not the case in your example.

[menem]

Then, from my previous file.
Please change formula in E3 to

Please Login or Register  to view this content.

Regards.

After review. this formula didn't work. :'(

[Metoo7]

finally, I have to use a help table. Please see attached workbook for my working.

[menem]

Another try.
Base from my previous file (again).
E3

Please Login or Register  to view this content.

Regards.

[davsth]

Jeff

although the loop doesn't appear to be working. Do I have to execute the macro until C23=0? well the macro loops until this happens, you just need to press the button and enable macros if you haven't already done so. As the rand function is dynamic it changes after a solution is found. To overcome this the results are pasted into the yellow area as static results

any clearer?