+ Reply to Thread
Results 1 to 6 of 6

Random sort without duplicates.

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    cornwall england
    MS-Off Ver
    Excel 2007
    Posts
    3

    Random sort without duplicates.

    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
    Attached Files Attached Files
    Last edited by taylanfin; 07-24-2013 at 02:57 PM.

  2. #2
    Registered User
    Join Date
    07-22-2013
    Location
    cornwall england
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Random sort without duplicates.

    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.

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Random sort without duplicates.

    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
    Attached Files Attached Files
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    07-22-2013
    Location
    cornwall england
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Random sort without duplicates.

    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.

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: Random sort without duplicates.

    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

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Random sort without duplicates.

    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
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Random Sample without duplicates
    By tjroby in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-04-2013, 07:11 PM
  2. random numbers without duplicates
    By squirellyd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2013, 06:33 PM
  3. [SOLVED] Random names without any duplicates
    By wessie in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-15-2012, 04:47 PM
  4. Random from list...without duplicates
    By HuskerBronco in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-23-2010, 08:17 AM
  5. Random Numbers With No Duplicates
    By DCSwearingen in forum Excel General
    Replies: 8
    Last Post: 01-31-2010, 02:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1