Random sort without duplicates.

[taylanfin]

Hi all. I hope that I have posted my question in the correct location. I am a complete novice on excel so i hope you will be able to assist me. Since before office in the days of super calc i have been running a local pub league which consists of 48 teams divided into 4 sections. i am now running excel 2007 and the system i blundered my way through by trial and error works fine.
There has always been one thing that i have never found an answer for so i thought i would ask here to see if what i want to do is possible.
When the season is ready to start i have a list of the 48 teams in column a. in column b i have a rand sort and in column c and d i have 4 sections. i then run a macro i recorded which states randomly sort column a, copy the first 12 teams and put in section 1 ,the second 12 in section 2 third in three and last 12 in 4.this works fine but here is the problem. A lot of the pubs have more than one team, some have 2 some have 4 etc. what i would like to be able to do is not have duplicates in the same section so if a pub has 4 teams i would like excel to automatically place them in 4 different sections instead of me having to do it manually. The same goes for 2 or 3 teams from the same pub. If someone would be able to look at it for me and tell me if it is possible i would be very grateful.As this is my first post Please let me know if any more info is needed

[taylanfin]

Hi all.
My post is now on page 20 without a reply so I am assuming that it is a little bit too complicated for the forum or I have done my post completely incorrect.If I may ask a question as this problem has been bugging me for many years does anyone know of a firm in the uk that I could approach the problem with?As we are a non profit league we have not got massive funds but would be happy too pay a small fee to get it looked at.
Many thanks in anticipation,
Taylanfin.

[HaHoBe]

Hi, taylanfin,

please find the attached workbook and have a look at the suggestions in Sheet TEAMS (2). I changed the forumla from Rand to RandBetween and placed fix numbers in for the pubs which have multiple teams.

The code for putting out the teams has been reworked as well:

Please Login or Register  to view this content.

Have a look please - I donīt know if the code is foolproof but in the situation laid down there I canīt find any doubles in one league/section. As I used Excel2013 on this file you might encounter problems when using RandBetween with Excel2003 or earlier (Analysis Tollpack AddIn must be activated) and for the colours used.

Ciao,
Holger

[taylanfin]

Hi Holger.
Thank you for having a look at I will give it a try and see how i get on.I am using excel 2007.Do I still need to install (Analysis Toolpack Addin)
Cheers,
Taylanfin.

[HaHoBe]

Hi, taylanfin,

AFAIR from 2007 on the toolpack has become part of the "normal" Excel, no need for activating the AddIn there. It was just some information in case you still work with a version up to 2003 where you could face an error due to a not activated addin.

Ciao,
Holger

[martindwilson]

for some reason excel seems not to translate ZUFALLSBEREICH to RANDBETWEEN well mine doesn't so you may have to do find ZUFALLSBEREICH replace with RANDBETWEEN on col C