Random Team Generator formula needed

**zboy365** · 08-24-2012, 01:14 AM

Hello all,

Like I explained in my introduction thread, I was driven here because I need a solution for my Madden 13 Online league. My friends and I would get together and do a fantasy draft online to create random teams within the game. In the new game, Madden 13, this feature is gone. However, I wanted to know if anyone knew a formula to designate any player names i put in on an excel sheet to 32 teams, randomly.

Heres what I mean:

I input 32 Quarterbacks into the excel doc, I input 32 Team names into the Excel doc, I want to scramble these 32 guys to random teams.

I have a sheet that randomly generates teams..but it only does it for 4 teams and I can not figure out how to adjust the formula for 32 nfl football teams. I uploaded the excel file i found and was trying to use.

Again it is using only 4 teams, I need 32, and for each team to only get 1 player...

any help or direction is GREATLY appreciated!

Thanks!

Team-Generator1.xls

**zboy365** · 08-24-2012, 01:17 AM

ps i found the file from here

http://www.get-digital-help.com/2010...ator-in-excel/

I tried figuring it out myself but the formula language was way above me..

**Max, Singapore** · 08-24-2012, 02:21 AM

I'm focusing on your lines below as your intents ..
> .. I input 32 Quarterbacks into the excel doc,
> .. I input 32 Team names into the Excel doc,
> .. I want to scramble these 32 guys to random teams

Here's a simple way ...
Assume the 32 QBs are entered into A2:A33
Assume the 32 Teams are entered into B2:B33
Put in C2: =INDEX($A$2:$A$33,RANK(D2,$D$2:$D$33))
Put in D2: =RAND()
Copy C2:D2 down to D33. Hide away col D
Col C returns a random scramble of the 32 QBs aligned with the 32 Teams
You can press F9 to generate a fresh scramble

**zboy365** · 08-24-2012, 02:54 AM

Max,

Thank you it works for me!

---------- Post added at 02:54 AM ---------- Previous post was at 02:49 AM ----------

quick question,

The columns with the randomly generated items in c2 through c 33 are all formulas. Is there a simple way to extract their names to save in another excel doc without re-typing their names? thanks.

**Max, Singapore** · 08-24-2012, 03:21 AM

Good to hear. To freeze the results, just copy cols B and C, then in a new sheet, do a paste special as values. Do click the little star at the bottom left of my responses to celebrate!

**zboy365** · 08-24-2012, 10:49 AM

nice, works as well thanks alot!!

**Cutter** · 08-24-2012, 01:46 PM

@ zboy365

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.