+ Reply to Thread
Results 1 to 10 of 10

Random from list...without duplicates

  1. #1
    Forum Contributor
    Join Date
    01-12-2004
    Location
    Nebraska, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Question Random from list...without duplicates

    Been scouring the Internet, and I can't seem to find an answer to my question. Is there a formula that I can put into a range of cells that will populate the cells with random names from a list...without duplicates?

    For example:

    I have a list of names in Sheet 2:Column A.

    In Column C:Sheet 1, I want a random selection of all of the names listed on Sheet 2...without duplicates.

    Then in Column D:Sheet 1, I want another list.

    Then, again...in Column E:Sheet 1. And so on.....


    I use the following array formula to populate each column:
    =INDEX(Sheet2!A:A,ROUNDUP(RAND()*COUNTA(Sheet2!A:A),0))
    ...but, I get duplicates. What am I missing?

    I sure hope someone can help me with this...

  2. #2
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    See if this gets you started.

    Input formula in worksheet(1) in cell C2 and copy down.

    Please Login or Register  to view this content.
    The formula is an-array needs to hold down,

    Ctrl,shift,Enter


    Hope it helps!
    Last edited by vane0326; 08-19-2007 at 12:12 PM.

  3. #3
    Forum Contributor
    Join Date
    01-12-2004
    Location
    Nebraska, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Thumbs up I'm getting closer. Thanks, vane.

    vane,

    Thank you, so much, for your quick response.
    Your formula is the closest I've come to making this thing work.

    Due to development of my spreadsheet, my list of names has moved from Column A to Column D on Sheet 2. Therefore, I made the necessary changes to your formula...which now looks like this:

    =INDEX(Sheet2!$D$2:$D$33,LARGE(IF(ISNA(MATCH(Sheet2!$D$2:$D$33,C$1:C1,0)),IF(Sheet2!D$2:$D$33<>"",ROW(Sheet2!D$2:$D$33)-ROW(Sheet2!$A$2)+1)),INT(RAND()*(ROW()+ROW(C$1))+1)))

    I copy/pasted your formula into my spreadsheet. I selected cells C2:C33 on Sheet 1. Then, I pasted your formula into the formula bar using CTRL+SHIFT+NET to be certain that the formula was entered as an array.

    I then copy/pasted the formula into cells D2:D33 on Sheet 1...to make sure that the formula was going to work across the entire spreadsheet.

    Both columns populated with names, and both were random selections, but...BOTH columns contained duplicates.
    Tapping F9 repopulates the lists, as expected, but again......with duplicates.

    Is there something more that I need to change in your formula?
    I've attached the spreadsheet for you to view, if you would like.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    I think the way you pasted the formula was wrong, you'll need to copy the formula below paste the formula in Sheet(1) in cell C2 double click inside the cell and then hold down,

    Ctrl,Shift,Enter

    and just copy down the formula.

    Please Login or Register  to view this content.

    Example workbook below.


    Note: Forgot to attach the file.
    Attached Files Attached Files
    Last edited by vane0326; 08-19-2007 at 07:10 PM.

  5. #5
    Forum Contributor
    Join Date
    01-12-2004
    Location
    Nebraska, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Unhappy Closer...still. But not there, yet.

    vane,

    I read your response and pasted the formula, as per your suggestion.

    The column (B2:B33) populated, but near the bottom of the column, I got several #NUM errors. I checked the forum for anything that I might have done incorrectly, and noticed that you had posted an amended Excel file. I opened the file, and scrolled down to discover that your spreadsheet was also returning #NUM errors towards the bottom of column B2:B33.

    I'm thinking I should have changed another part of your code. Your code, after my adjustments, contains this snippet towards the end of the code:

    ......ROW(Sheet2!$D$2:$D$33)-ROW(Sheet2!$A$2)+1)),........

    Shouldn't that $A$2 be changed to $D$2?

    I should mention, however, that where the code DID return names, there were no duplicates.
    Also, repeated F9 action changed the teams listed, but not with the desired result.

    In fact, look at the odd behavior that the spreadsheet exhibited:

    Cell C2 alternated through only 3 of the 32 names.
    Cell C3 alternated through only 5 of the 32 names. (The 3 names from C2...plus two new ones.)
    Cell C4 alternated through only 7 of the 32 names. (The 5 names from C3...plus two new ones.)
    Cell C5 alternated through only 9 of the 32 names. (The 7 names from C3...plus two new ones.)

    And this pattern continued down the column...with each cell selecting a random name from a pool just 2 names larger than the cell above, and not from the entire list of names. What the hey...????!!!

    Boy...I'd sure like to get this thing working. I am very confident that your formula is eventually going to produce the results that I am looking for.

    Please excuse my ignorance as I shamefully admit that I can only comprehend about half of what your formula is attempting to do. I can't thank you guys (and gals) enough for taking the time to answer us through this forum. Your knowledge and expertise extend well beyond any of the books that I've read on Excel. I hope, someday, to obtain enough knowledge in this field to join you in the forums as an educator, rather than a student.

  6. #6
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    I can not manage to see your attachment, I think it is an issue with the network at my work. However the following should work.

    I have created a list of random numbers by the side of the names on the names sheet. On the lookup sheet I just match the largest of these numbers in the list of numbers and offset by a column to get to the name by the side of it. Then the second largest etc, this should ensure no duplication.

    Is it on the right lines?

    Regards

    Dav
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-12-2004
    Location
    Nebraska, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Thumbs up Thanks, Dav......and Thanks, again, to you, vane.

    Hello dav,

    Your method was my on my list of "last resorts" as I need seventeen unique columns of randomly selected names. I was hoping I could use some kind of an array formula to generate the lists from ONE list of names...but it doesn't seem that Excel is capable of that.

    Instead, like you suggested, I made 17 columns of random numbers, and pasted my NAME_LIST in a column next to each one of them. I then created a Macro that selected each RAND_NUM/NAME_LIST pair, and performed a DATA SORT on each one.

    This produces the desired results, but was more work than I was hoping would be needed. I'm always looking for the EASY way out...even when the EASY way proves to be more of a headache to accomplish.

    Thanks, anyway, for taking time to address my dilemma.

  8. #8
    Registered User
    Join Date
    08-30-2007
    Posts
    4
    I want to arrange a list of students into random groups of three (or four, or five. depending in final class size)
    There will be 5 assignements and each time the groups are formed randomly. It doesnt matter if two students are working together for more than one case as long as the groups are assigned randomly.

    I have set up a excel sheet and everything works. The only thing I am stuck at is assinging the 'left-overs'. example: with 59 students and groups of 3 i have 19 groups of 3 and 1 group of 2 students.
    I dont want a group of two and if I add the leftovers to the last group i'll get a group of 5! i'd rather have two groups of 4.

    names in sheet are from google. so publicly available and randomly chosen.

    any suggestions on how to deal with the left-overs?
    Attached Files Attached Files
    Last edited by rooster2005; 08-30-2007 at 11:37 AM. Reason: attach file

  9. #9
    Registered User
    Join Date
    03-23-2010
    Location
    Holland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Random from list...without duplicates

    Hi all,

    Rooster2005 thanks for your excel file!

    This is what I'm looking for, but in your case it doesn't matter if two students are working together for more than one case.

    But in my case it DOES matter that a student is in the same group! It doesn't matter that two students will meet again, but the teacher has his own 'case' and needs to see every time new students.

    So, if a student has already been in group 1 (in case 1) he can't be in group 1 again in case 2 till 5...

    Could some please help me with this problem?
    I use the same excel file from rooster2005 (see his post).

    Thanks!

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Random from list...without duplicates

    Silverdust, please start your own thread.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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