Excel options

%3CLINGO-SUB%20id%3D%22lingo-sub-1481950%22%20slang%3D%22en-US%22%3EExcel%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1481950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22_5yl5%22%3E%3CSPAN%3EHi!%20I'm%20not%20an%20IT%20or%20an%20Excel%20specialist.%20I%20need%20some%20help.%20Can%20Excel%20generate%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22_5yl5%22%3E%3CSPAN%3Emathematical%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22_5yl5%22%3E%3CSPAN%3Ecombinations%20of%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22_5yl5%22%3E%3CSPAN%3Eelements%2Cwith%20specific%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22_5yl5%22%3E%3CSPAN%3Edata%3F%20For%20example%3Ai%20wanna%20create%20100%20teams%20of%2011%20players%2Cusing%20about%20150%20players%20in%20total%20and%20none%20of%20this%20players%20can't%20appear%20on%20more%20than%207%20teams.%20Can%20you%20help%20me%20out%20with%20something%20like%20that%3F%20Can%20i%20create%20something%20like%20this%20in%20Excel%20or%20maybe%20you%20guys%20know%20other%20programs%20that%20do%20that.%20Thanks%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1481950%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483707%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483707%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F707128%22%20target%3D%22_blank%22%3E%40Andrei35%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EFirst%20off%2C%20you%20need%20to%20know%20how%20many%20players%20you%20need%20so%20they%20can%20only%20appears%20in%20a%20maximum%20of%207%20teams.%3C%2FP%3E%3CP%3E100%20x%2011%20%3D%201100%3C%2FP%3E%3CP%3E1100%20%2F%207%20%3D%20157.14...%20(round%20up%20to%20158)%3C%2FP%3E%3CP%3EThen%20I%20created%20the%20100%20teams%20and%20give%20the%20players%20an%20ID%20%23.%20It's%20easier%20to%20do%20math%20with%20a%20number%20%3A).%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20that%2C%20all%20you%20need%20to%20do%20is%20to%20consecutively%20rotate%20through%20these%20numbers%20up%20to%20a%20maximum%20of%20158%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20with%20a%20vlookup%2C%20you%20can%20match%20the%20player%20ID%20with%20a%20players%20name.%20Look%20at%20the%20table%20from%20cell%20A18%20to%20B176.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%2C%20hope%20this%20helps%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483720%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F707128%22%20target%3D%22_blank%22%3E%40Andrei35%3C%2FA%3E%26nbsp%3B%20Excel%20could%20be%20helpful%20but%20won't%20just%20do%20it%20for%20you%20per%20se.%26nbsp%3B%20That%20said%20you%20need%20100%20teams%20of%2011%20players%20(total%20of%201100%20slots)%20and%20have%20150%20players%20with%20a%20max%20of%207%20per%20individual%20(1050%20slots%20max).%26nbsp%3B%20It%20would%20appear%20you%20have%20a%20problem%20...%3C%2FP%3E%3CP%3EThat%20said%2C%20I%20think%20you%20could%20have%20your%20list%20in%20col%20A%20then%20create%20a%20col%20that%20has%20that%20list%20repeated%207x%20with%20each%20in%20a%20random%20order.%26nbsp%3B%20The%20every%2011%20rows%20becomes%20a%20team.%26nbsp%3B%20Again%2C%20as%20of%20now%2C%20you%20don't%20have%20enough%20players.%3C%2FP%3E%3CP%3EIn%20the%20attached%20sheet%20I%20have%20a%20col%20for%20player%20names%20(col%20A)%20and%20then%20col%20B%20has%20that%20list%20randomized%207x%20the%20number%20of%20players%20in%20col%20A.%26nbsp%3B%20The%20random%20numbers%20are%20on%20sheetB%20BUT%20what%20I%20did%20was%20use%20cols%20A%3AG%20to%20generate%20random%20numbers%20and%20then%20copied%20those%20numbers%20onto%20cols%20H%3AN%20as%20VALUES%20so%20the%20order%20doesn't%20keep%20changing%20every%20time%20you%20hit%20enter%20on%20the%20sheet.%26nbsp%3B%20To%20create%20a%20new%20list%20simply%20copy%20the%20values%20from%20cols%20A%3AG%20and%20paste%20as%20VALUES%20onto%20cols%20H%3AN%3C%2FP%3E%3CP%3EI%20added%20the%20letters%20a%3Az%20as%20player%20names%20(26%20total)%20and%20filled%20col%20B%20with%20the%20formula%20down%20to%20row%20200%20but%20you%20will%20see%20that%20after%20row%20183%20(26%20x%207%20%3D%20182%20%2B%201%20because%20it%20starts%20on%20row%202%20%3D%20183)%20the%20values%20show%20%23N%2FA%20because%20you%20ran%20out%20of%20players.%26nbsp%3B%20You%20will%20have%20to%20fill%20down%20to%20row%201101.%3C%2FP%3E%3CP%3EI%20created%20a%20easy%20to%20read%20list%20of%20Teams%20labelled%20Team%201%2C%20Team%202%2C...%20to%20show%20that%20portion%20of%20col%20B%20in%20a%20convenient%20display%20(i.e.%20Team%201%20is%20rows%202%3A12%2C%20Team%202%20is%20rows%2013%3A23%2C%20etc...).%26nbsp%3B%20Again%20you%20will%20have%20to%20fill%20right%20to%20create%20the%20rest%20of%20the%20teams%20if%20you%20want%20that%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20Hi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%20%2C%20I%20just%20saw%20we%20both%20worked%20on%20this%20question%20at%20the%20same%20time%20and%20both%20found%20the%20number%20of%20players%20short.%26nbsp%3B%20I'm%20sure%20between%20us%20Andrei%20should%20have%20something%20that%20works.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483742%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20options%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483742%22%20slang%3D%22en-US%22%3EThere%20is%20no%20built-in%20functionality%20in%20Excel%2C%20perhaps%20you%20might%20just%20google%20for%20%22Generate%20teams%20from%20names%20list%22%20or%20similar%3F%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi! I'm not an IT or an Excel specialist. I need some help. Can Excel generate mathematical combinations of elements,with specific data? For example:i wanna create 100 teams of 11 players,using about 150 players in total and none of this players can't appear on more than 7 teams. Can you help me out with something like that? Can i create something like this in Excel or maybe you guys know other programs that do that. Thanks

3 Replies

Hi @Andrei35,

First off, you need to know how many players you need so they can only appears in a maximum of 7 teams.

100 x 11 = 1100

1100 / 7 = 157.14... (round up to 158)

Then I created the 100 teams and give the players an ID #. It's easier to do math with a number :). 

After that, all you need to do is to consecutively rotate through these numbers up to a maximum of 158 

Finally, with a vlookup, you can match the player ID with a players name. Look at the table from cell A18 to B176.

 

See attached, hope this helps

Ben

 

@Andrei35  Excel could be helpful but won't just do it for you per se.  That said you need 100 teams of 11 players (total of 1100 slots) and have 150 players with a max of 7 per individual (1050 slots max).  It would appear you have a problem ...

That said, I think you could have your list in col A then create a col that has that list repeated 7x with each in a random order.  The every 11 rows becomes a team.  Again, as of now, you don't have enough players.

In the attached sheet I have a col for player names (col A) and then col B has that list randomized 7x the number of players in col A.  The random numbers are on sheetB BUT what I did was use cols A:G to generate random numbers and then copied those numbers onto cols H:N as VALUES so the order doesn't keep changing every time you hit enter on the sheet.  To create a new list simply copy the values from cols A:G and paste as VALUES onto cols H:N

I added the letters a:z as player names (26 total) and filled col B with the formula down to row 200 but you will see that after row 183 (26 x 7 = 182 + 1 because it starts on row 2 = 183) the values show #N/A because you ran out of players.  You will have to fill down to row 1101.

I created a easy to read list of Teams labelled Team 1, Team 2,... to show that portion of col B in a convenient display (i.e. Team 1 is rows 2:12, Team 2 is rows 13:23, etc...).  Again you will have to fill right to create the rest of the teams if you want that format.

 

Hope it works for you.

 

 

EDIT: Hi @Bennadeau , I just saw we both worked on this question at the same time and both found the number of players short.  I'm sure between us Andrei should have something that works.

 

There is no built-in functionality in Excel, perhaps you might just google for "Generate teams from names list" or similar?