Duplication cell value

%3CLINGO-SUB%20id%3D%22lingo-sub-1927261%22%20slang%3D%22en-US%22%3EDuplication%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1927261%22%20slang%3D%22en-US%22%3E%3CP%3EI%20work%20in%20a%20school%20and%20am%20putting%20together%20a%20new%20reward%20system.%20The%20pupils%20get%20points%20based%20on%20numerous%20factors%20which%20gives%20them%20a%20score.%20Every%207%2F8%20weeks%2C%20we're%20going%20to%20have%20a%20draw%20to%20select%20a%20winner%2C%20but%20I%20want%20their%20total%20points%20to%20dictate%20how%20many%20entries%20they%20receive%20to%20that%20draw.%3C%2FP%3E%3CP%3EI%20have%20their%20names%20in%20Column%20A%20and%20column%20B%20states%20their%20number%20of%20entries.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20online%20random%20name%20selector%2C%20I%20need%20to%20be%20able%20to%20duplicate%20their%20names%20based%20on%20the%20value%20of%20column%20B%20so%20Pupil%20A%20might%20be%20listed%2032%20times%2C%20but%20Pupil%20F%20only%205...%3C%2FP%3E%3CP%3EI%20could%20do%20this%20manually...%20but%20each%20of%20our%20year%20groups%20have%20200%20pupils%20so%20it%20would%20take%20ages!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1927261%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1927300%22%20slang%3D%22en-US%22%3ERe%3A%20Duplication%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1927300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F880112%22%20target%3D%22_blank%22%3E%40Eddietheteach%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20it%20be%20possible%20for%20you%20to%20post%20a%20file%20that%20resembles%20your%20actual%2C%20just%20replacing%20any%20real%20names%20with%20those%20of%20Disney%20characters%20(or%20Star%20Wars%2C%20or....you%20name%20it)....%26nbsp%3B%26nbsp%3B%20It%20would%20make%20it%20a%20lot%20easier%20for%20people%20here%20to%20help%20you%2C%20and%20be%20working%20with%20your%20actual%20layout.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20please%2C%20if%20you%20could%2C%20clarify%20how%20you're%20using%20this.%20From%20what%20you've%20said%2C%20it%20sounds%20as%20if%20there%20will%20only%20be%20one%20name%20selected%20(if%20that's%20not%20the%20case%2C%20please%20clarify)%3B%20that%20one%20person%20might%20in%20fact%20only%20have%20her%20or%20his%20name%20in%20the%20%22pot%22%205%20times%2C%20somebody%20else%2040%20or%20more%20times....so%20there's%20a%20greater%20probability%20of%20selecting%20the%20person%20with%2040%20entries%2C%20but%20the%20one%20with%205%20still%20has%20a%20chance.%20If%20that's%20not%20correct%2C%20please%20clarify.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1927410%22%20slang%3D%22en-US%22%3ERe%3A%20Duplication%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1927410%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BYes%2C%20you're%20spot%20on.%20They're%20all%20equal%20-%20but%20some%20are%20more%20equal%20than%20others.%20They'll%20be%20three%20winners%20in%20effect%2C%20but%20I'd%20just%20run%20the%20selector%20three%20times%20(or%20re-run%20if%20the%20same%20person%20was%20selected%20twice)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20person%20would%20go%20into%20the%20pot%20multiple%20times%20depending%20on%20how%20many%20entries%20they%20had%20earned.%20In%20the%20full%20list%2C%20the%20greatest%20goes%20in%2029%20times%2C%20the%20least%20goes%20in%20twice.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I work in a school and am putting together a new reward system. The pupils get points based on numerous factors which gives them a score. Every 7/8 weeks, we're going to have a draw to select a winner, but I want their total points to dictate how many entries they receive to that draw.

I have their names in Column A and column B states their number of entries.

 

For the online random name selector, I need to be able to duplicate their names based on the value of column B so Pupil A might be listed 32 times, but Pupil F only 5...

I could do this manually... but each of our year groups have 200 pupils so it would take ages! 

4 Replies

@Eddietheteach 

 

Would it be possible for you to post a file that resembles your actual, just replacing any real names with those of Disney characters (or Star Wars, or....you name it)....   It would make it a lot easier for people here to help you, and be working with your actual layout.

 

Also please, if you could, clarify how you're using this. From what you've said, it sounds as if there will only be one name selected (if that's not the case, please clarify); that one person might in fact only have her or his name in the "pot" 5 times, somebody else 40 or more times....so there's a greater probability of selecting the person with 40 entries, but the one with 5 still has a chance. If that's not correct, please clarify.

 

@mathetes Yes, you're spot on. They're all equal - but some are more equal than others. They'll be three winners in effect, but I'd just run the selector three times (or re-run if the same person was selected twice)

 

Each person would go into the pot multiple times depending on how many entries they had earned. In the full list, the greatest goes in 29 times, the least goes in twice. 

@Eddietheteach 

 

Here's a quick and dirty--but I think effective in achieving the desired result. I just multiplied the number of entries by a random fraction. In test runs (activated by the F9 key) it randomly picked people down on the list, but did a higher percentage of those near the top, which is what you'd expect.

 

The key formula just matches the max (highest) product of entries times the random number...and gives the name associated with it.

@mathetes so straightforward! (once you know how...)

 

I've never used RAND before so thankyou. Just to set it up with the full list and draw my winners...