+ Reply to Thread
Results 1 to 7 of 7

Creating multiple column randomized long list out of 114 names

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Creating multiple column randomized long list out of 114 names

    Dear all,

    I need to distribute 114 names across 10 columns and 3000 rows with the condition that no name is to be repeated per row across the ten columns.

    Can I get some help with this question please?

    Many thanks in advance.

    T.

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Creating multiple column randomized long list out of 114 names

    I am not clear what you are asking, why not include an attachment , scaled down if necessary

    do you wish to have a random sample of 10 names in each of 3000 rows? just making sure the same name doesn't appear twice?

    so the same name will on average appear around 250 times or be it in different rows?

  3. #3
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Creating multiple column randomized long list out of 114 names

    Thanks davsth for your reply. My apologies if my question was not clear enough. Yes, I have a pool of 114 names (A1:A114) in Sheet1. I need to randomize those names in Sheet2 across 10 columns (A:J) and deep down to 3000 rows. The condition of randomizing here is that no row of those 3000 rows should have a name duplicated in its 10 cells across the 10 columns.

    I hope I made it a bit clearer.

    Once again, thank you.

    T.

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Creating multiple column randomized long list out of 114 names

    I would use the analysis tools add in and create 20 variables and 3000 rows from a uniform distribution between 1 and 115

    then in the next 20 columns truncate the values int(a1) etc
    then leave a column blank (AO)
    so in ap2
    =(COUNTIFS($U2:U2,U2,$U2:U2,"<>115")=1)*1+AO2

    then finally to your results in the first cell you want your master list in
    =INDEX($U2:$AN2,MATCH(COLUMN(A2),$AP2:$BI2,0))

    where u2:an2 contain the 20 columns of potential values

    If needed you could then lookup the numbers to return names from this

    hopefully a start

  5. #5
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Creating multiple column randomized long list out of 114 names

    Another Option :-
    Please Login or Register  to view this content.
    Regards Mick

  6. #6
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Creating multiple column randomized long list out of 114 names

    Marvellous Mick, really marvellous. I spent the entire day trying to solve this problem which you solved in a few seconds with your genius code.
    I'm more than thankful to you Mick, please accept my best regards.

    T.

  7. #7
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Creating multiple column randomized long list out of 114 names

    You're welcome

+ 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. Replies: 15
    Last Post: 08-10-2017, 02:08 AM
  2. Replies: 1
    Last Post: 07-20-2017, 01:47 AM
  3. Replies: 1
    Last Post: 07-20-2017, 01:44 AM
  4. [SOLVED] VBA help required: Creating multiple workbooks from a template and a list of names
    By CarolynL in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2013, 04:16 AM
  5. [SOLVED] Creating multiple workbooks from a list of names
    By gibson2503 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-24-2013, 05:40 PM
  6. [SOLVED] names in a long list
    By ferde in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2006, 09:00 PM
  7. [SOLVED] Setting up a random list from long list of names ?
    By yorkshire exile in forum Excel General
    Replies: 4
    Last Post: 01-06-2005, 10:06 AM

Tags for this Thread

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