Random from list...without duplicates

1. 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. 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!

3. 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.

4. 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.

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

vane,

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. 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

7. 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. 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?

9. 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...

I use the same excel file from rooster2005 (see his post).

Thanks!

10. Re: Random from list...without duplicates

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

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