+ Reply to Thread
Results 1 to 14 of 14

Random Numbers assigned to static values multiple times no duplicates....?

Hybrid View

  1. #1
    Registered User
    Join Date
    11-23-2014
    Location
    Louisiana
    MS-Off Ver
    2013
    Posts
    5

    Random Numbers assigned to static values multiple times no duplicates....?

    What im trying to accomplish is this, We have 12 kids and and 6 Families. I want to be able to assign the kids to each family in a way they will not duplicate more than once for anyone family and the family ends with three kids to give presets to(not their own kids). Each family needs to be assigned three kids non of which can be duplicate for that family. Trying to make random lottery style sheet for giving kids presents. Guys i know this sounds crazy. I may be over complicating this, but I know there has to be a way. I have been messing around with the Rand function in excel, (and maybe this is more of an access goal), but if you could set up the randbetween function to not equal itself in another cell this could work possibly. But I haven't figured this out.

    Screenshot 2014-11-23 19.22.35.jpg

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    I can't see your image.

    See if this helps:

    http://mcgimpsey.com/excel/udfs/randint.html
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    You could try this
    family presents.xlsm

    It takes the 6 families and assumes 2 presents per family distributed to the 12 kids (ie 1 present per kid) and excludes the kids in their own family.

    You can put up to 3 kids into a family (left side of spreadsheet - yellow area - which will ensure they are excluded from their own family giving) and press the run button to generate the distribution. It only really random generates the top line then allocates based on the remaining availability.
    Happy with my advice? Click on the * reputation button below

  4. #4
    Registered User
    Join Date
    11-23-2014
    Location
    Louisiana
    MS-Off Ver
    2013
    Posts
    5

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    This is so close I can taste it. YOU ARE A LIFE SAVER. I want to be able to assign 3 presents to each family. Where in to formula do i need to change?Microsoft Excel - family presents (1)_2014-11-24_10-41-16.png

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

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    If each of 6 families gives 3 presents, and there are 12 kids, 6 kids will get 2 presents each, and 6 will only get 1. That's going to make for a disturbing Chrstmas morning.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    11-23-2014
    Location
    Louisiana
    MS-Off Ver
    2013
    Posts
    5

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    Lets look at it like this then... instead of grouping families together, lets say you have 12 adults giving presents to 12 children. but you want each family to select three children that are not there own. Im confusing my self now, Maybe I will just put names in a hat. But i really want to figure this out now.

  7. #7
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    Like SHG said I can change the numbers but 3 presents per family = 18 so 1.5 presents per kid is a problem

  8. #8
    Registered User
    Join Date
    11-23-2014
    Location
    Louisiana
    MS-Off Ver
    2013
    Posts
    5

    Re: Random Numbers assigned to static values multiple times no duplicates....?


  9. #9
    Registered User
    Join Date
    11-23-2014
    Location
    Louisiana
    MS-Off Ver
    2013
    Posts
    5

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    Ok im a fool guys, I was looking at this wrong according to my wife, Nuff said. She wants each kid to get three presents, making each family buying 6 gifts. So this should work now. Can we alter the sheet above to accommodate?

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    I'll get to it in a few hours and get back to you

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

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    See if this works for you.
    Attached Files Attached Files

  12. #12
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    Here you go. I took SHGs excellent work and added a macro to recalcualte until a solution is found. I also played withthe names to see if it worked for families with 3 kids. It looks like it works fine

    scratch(1).xlsm

  13. #13
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    I see SHG has a neat solution. Does this work for you? Mine isn't as easy when you get more combinations. I can work on it if SHGs solution isn't what you want but it might be a day til I get back to it

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

    Re: Random Numbers assigned to static values multiple times no duplicates....?

    Crooza's macro to recalculate until you get a stable solution would be a good addition.

+ 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. [SOLVED] Random Numbers without duplicates
    By Sean Thomas in forum Excel General
    Replies: 3
    Last Post: 11-23-2014, 01:58 PM
  2. Print to text file and Static random numbers?
    By xizor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-10-2014, 03:58 PM
  3. Print to text file and Static random numbers.
    By xizor in forum Excel General
    Replies: 0
    Last Post: 06-28-2014, 10:46 PM
  4. Excel 2007 : Random numbers and static numbers
    By ugsquish in forum Excel General
    Replies: 3
    Last Post: 01-17-2012, 08:16 PM
  5. Random numbers plus assigned number
    By mutchdea in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2009, 09:57 PM

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