Create Random Team based on Skill Level & Presence

[GinaMoses]

Hello,

I'm trying to generate a random team list (which I believe I have in the attached spreadsheet), however, I want the teams to have an even amount of skilled players and the teams to be listed based on if they show up or not.. ie: present. Can someone help me create the criteria formula for skill & presence please?

Thanks,
Gina

 

[p45cal]

In the attached:
In sheet Generator I've gone part of the way by dealing with the Present/Absent part:
A changed formula in column C, likewise column F.

In sheet Generator (2) there's a Power Query solution which deals with both the distribution of skills and present/absent:
The result table at cell F4 displays the results columnwise.
It needs updating like a pivot table by right-clicking the table and choosing Refresh.
Each refresh should change the teams.
The source data table on the left is now a proper Excel table; it should change its size automatically as you remove/add players but you may need to check the extents of that table (grab-handle at bottom right of the table).
The cell B2 is a named range (TeamSize) which the query uses.
There's conditional formatting in that table to help separate the teams visually.
The Power Query query could be more streamlined, it's just been left as it was after I first managed to get it working.
If you don't want the Skill column in the result table that's easy enough to tweak - just there to allow easy checking.

 

[GinaMoses]

This is great. I appreciate your work as well as the retiree golf teams.

 

[bosco_yip]

Try this formula way,

In N5 enter formula:

=IFERROR(INDEX(FILTER($B$5:$B$27,($F$5:$F$27=N$4)*($G$5:$G$27="X")),SEQUENCE($B$2,1)),"")

Then copied formula across right to R5.

 

[GinaMoses]

Try this formula way,

In N5 enter formula:

=IFERROR(INDEX(FILTER($B$5:$B$27,($F$5:$F$27=N$4)*($G$5:$G$27="X")),SEQUENCE($B$2,1)),"")

Then copied formula across right to R5.

View attachment 83065

This works too. Thanks so much!

 

[p45cal]

So can you please explain a bit more about the Power Query solution in sheet Generator (2)?

That would take ages! However, I can analogise:
Take a jar for each level of skill and put all the players of the same skill level together in their jar (Step 9 Grouped Rows of the query in the attached).
Line the jars up, then working from left to right, take one player at random out of each jar in turn and line them up in one long line.
Then if there are to be, say, 5 members per team, grab the first 5 from that long line and call them team 1, take the next 5 and call them team 2 etc. (Step 17 Custom1 of the query in the attached).

do you mean that refreshing the table will randomly generate new teams based on the present/absent and skill criteria?

Yes.

After posting that file I did some further testing and found that the teams were not as random as they could be (players lower down in the source list tended always to end up towards the bottom of the results list), so added another level of randomnisation in the attached.

 

[GinaMoses]

Hey guys! All of the spreadsheets have worked whether with indexing or using the power queries however none of these work with Excel version 2016. The older golfers that are using the team generator have switched to an older laptop at the clubhouse and the spreadsheets are giving errors. Can anyone help with a compatible version please?

 

[p45cal]

The Power Query solution should work with Excel 2016 because PQ is built-in to that version. However there may be PQ version differences. What happens when they try the PQ solution?

 

[GinaMoses]

It gives an expression error: 5 arguments were passed to a function which expects between 2 and 4

 

[p45cal]

I have attached a tweaked version of the file attached to msg#6 with changes I know to be incompatible with older versions of Power Query; if it still doesn't work I need to know the errors it reports.

edit after posting:

It gives an expression error: 5 arguments were passed to a function which expects between 2 and 4

Yes, it looks to be the same sort of error.

 

[GinaMoses]

I have attached a tweaked version of the file attached to msg#6 with changes I know to be incompatible with older versions of Power Query; if it still doesn't work I need to know the errors it reports.

edit after posting:
Yes, it looks to be the same sort of error.

Thank you! I will try this and let you know.

 

[GinaMoses]

Works great!!! Thanks again!