The following attacted program is a name generator. It randomly generates names from the Roster sheet to the shuffle sheet (to be randomized) then displays it on the watchbill sheet. The problem I'm having is that the names seem to be repeating themselves before the entire list is used at least once from the roster sheet. I need all the names to generate at least once then have it fairly repeat the names (but not in the same columns on the watchbill sheet). Can this be done using the formulas I have? I've been trying to figure this out forever, its just a little beyond my level. Please help.
Prevent Duplicates in Formula Generating Same Names
-
-
Re: Prevent Duplicates in Random Generation
Create a uniue list of the names.
Add this Formula in an adjacent column =RAND() and Copy down
Sort both columns by the random numbers.
Now use a simple reference formula where needed to each name in the list. -
Re: Prevent Duplicates in Random Generation
Quote from Dave HawleyCreate a uniue list of the names.
Add this Formula in an adjacent column =RAND() and Copy down
Sort both columns by the random numbers.
Now use a simple reference formula where needed to each name in the list.That's a neat trick. I'll remember that. It get's rid of the problem of when you get down to the last few entries and are using RND to select from an array.
-
Re: Prevent Duplicates in Random Generation
The sorting can be automated via the Calculate Event if needed.
-
Re: Prevent Duplicates in Random Generation
Don,
I've had a look at your spreadsheet and it appears to be working correctly. Due to the unique names the names can only be selected once. I don't think the error is in the shuffle sheet or the technique that Dave gave you.
It's too late here to start debugging but I'll check tomorrow and if no one has answered, I'll have another look.
So where is USS Essex now or is that classified ?? :sailboat:
Regards
Rich
-
Re: Prevent Duplicates in Random Generation
Quote from don juanDave,
is that not what I already did on the shuffle sheet? Exactly as you described. It generates names but unfairly. Its not using the entire list before cycling back to use repeat names to fill the cells.
No. You have formulae generating the names.
Just create a static list of the names, starting from A1 on any sheet. Use the RAND & sort as suggested. Then, where you want the non repeating names use =Sheet1!A1, =Sheet1!A2 etc.
-
Re: Prevent Duplicates in Formula Generating Same Names
I don't know how else to explain this to you. All I can do is show you a Workking example. I believe I did this a few weeks back?
Push F9 to randomize the names. The code is in the Sheet Calculate Event. Right click on the Name List sheet and choose View Code.
-
Re: Prevent Duplicates in Formula Generating Same Names
No, it's not the exact same. You list on the Shuffle sheet has duplicates.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!