+ Reply to Thread
Results 1 to 16 of 16

Random Lists with no Repeats

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    8

    Random Lists with no Repeats

    Hello,

    I am trying to create a random menu of meals to eat. I have made a list of 20 meals and I want excel to spit out 10 of those 20 at random which I have achieved, some meals have multiple options so I have a if x then a variation pops up as well. It is all working well but I am getting repeats. Is there a way to create my list of 10 with sub categories chosen without getting any repeats? The formula I am using in column G is - =RANDBETWEEN(1,20) with the formula in column H being =IF(G4=1,RANDBETWEEN(1,4),IF(G4=6,RANDBETWEEN(1,3),IF(G4=7,RANDBETWEEN(1,3),IF(G4=15,RANDBETWEEN(1,2)))))

    Also, how do I get it to spit out a name rather than the number that coorisponds to the meal?

    example.jpg

    Thank you in advance for looking and helping me out.
    Attached Images Attached Images

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,165

    Re: Random Lists with no Repeats

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Pictures are rarely much use when you have the workbook available.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-11-2017
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    8

    Re: Random Lists with no Repeats

    Silly queston, how do I upload a workbook? All I see is image upload?

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

    Re: Random Lists with no Repeats

    To attach a file to your post...

    Click the Reply button to open the Reply Editor
    Click the Go Advanced button
    Scroll down until you see the Manage Attachments link and click that
    Click the Browse button and select your file
    Click the Open button
    Click the Upload button
    Click the Close Window button
    Click the Submit Reply button
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    01-11-2017
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    8

    Re: Random Lists with no Repeats

    Here is a copy of my workbook.
    Attached Files Attached Files

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

    Re: Random Lists with no Repeats

    Instead of having subcategories why not just do like this...

    Data Range
    A
    B
    4
    1
    BBQ Chicken
    5
    2
    BBQ Ribs
    6
    3
    BBQ Steak
    7
    4
    BBQ Pork
    8
    5
    Sushi
    9
    6
    Roast
    10
    7
    Pasta
    11
    8
    Mexican
    12
    9
    Salad Cesar
    13
    10
    Salad Garden
    14
    11
    Salad Chicken
    15
    ------


    Then, to do what you want would be a bit easier.

  7. #7
    Registered User
    Join Date
    01-11-2017
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    8

    Re: Random Lists with no Repeats

    I'm trying to limit the number of times I lets say use the BBQ. If I do it that way I could end up BBQing four of the nights rather than having variation.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: Random Lists with no Repeats

    I changed your red numbers in column C, so that they reflect the main menu numbers more readily, i.e. 11, 12, 13, 14, 61, 62, 63, 71, 72, 73, 151, 152.

    Then I put this formula in F4:

    =RAND()

    and copied it down to F23. Then you can put this one in G4:

    =RANK(F4,$F$4:$F$23)+COUNTIF(F$4:F4,F4)-1

    and this one in H4:

    =IF(G4=1,RANDBETWEEN(11,14),IF(G4=6,RANDBETWEEN(61,63),IF(G4=7,RANDBETWEEN(71,73),IF(G4=15,RANDBETWEEN(151,152),""))))

    Both of these two can also be copied down to row 23.

    Finally, I put this formula in J4:

    =IFERROR(VLOOKUP(H4,C:D,2,0)&" "&VLOOKUP(G4,A:C,3,0),VLOOKUP(G4,A:C,3,0))

    and this is only copied down to J13, to give you the 10 meals that you asked for, randomly chosen with no duplicates. If you want a different selection, just press the F9 key.

    Hope this helps.

    Pete

    EDIT: By the way, you have a 9 - Soup and a 15 - Soup. I presume the entry for 9 should be something else.

    Pete
    Last edited by Pete_UK; 01-28-2017 at 03:52 PM.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,488

    Re: Random Lists with no Repeats

    Try

    in H4 and copy down

    =IF(G4=1,INDEX($D$5:$D$8,RANDBETWEEN(1,4)),IF(G4=6,INDEX($D$14:$D$16,RANDBETWEEN(1,3)),IF(G4=7,INDEX($D$18:$D$20,RANDBETWEEN(1,3)),IF(G4=15,INDEX($D$29:$D$30,RANDBETWEEN(1,2)),INDEX($C$4:$C$35,MATCH(G4,$A$4:$A$35,0))))))

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

    Re: Random Lists with no Repeats

    Try this...

    Setup the meal list as such:

    Data Range
    A
    B
    C
    D
    E
    F
    1
    Number
    Meals
    2
    1
    BBQ
    Chicken
    Ribs
    Steak
    Pork
    3
    2
    Sushi
    4
    3
    Roast
    5
    4
    Pasta
    6
    5
    Mexican
    7
    6
    Salad
    Cesar
    Garden
    Chicken
    8
    7
    Pizza
    Peperoni
    Hawaiian
    Veggie
    9
    8
    Curry
    10
    9
    Kraut and Chops
    11
    10
    Beef and Broccoli Stirfry
    12
    11
    Shepherds Pie
    13
    12
    Calzones
    14
    13
    Kabobs
    15
    14
    Wraps
    16
    15
    Soup
    Tomato
    French Onion
    17
    16
    Lasagna
    18
    17
    Lemon Chicken
    19
    18
    Fish
    20
    19
    Corn Fritters
    21
    20
    Veggie Supsise


    Then, to pick 10 random meals...

    Data Range
    H
    I
    J
    1
    Number
    Meal
    Sub Category
    2
    8
    Curry
    3
    9
    Kraut and Chops
    4
    7
    Pizza
    Veggie
    5
    5
    Mexican
    6
    15
    Soup
    French Onion
    7
    6
    Salad
    Cesar
    8
    3
    Roast
    9
    11
    Shepherds Pie
    10
    13
    Kabobs
    11
    18
    Fish


    Enter this array formula** in H2:

    =LARGE(ROW(INDIRECT("1:20"))*(COUNTIF(H$1:H1,ROW(INDIRECT("1:20")))=0),RANDBETWEEN(1,21-ROWS(H$1:H1)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    That will generate a random number from 1 to 20 without repeats.

    Enter this formula in I2:

    =VLOOKUP(H2,A$2:B$21,2,0)

    Enter this formula in J2:

    =T(INDEX(C$2:F$21,H2,IFERROR(RANDBETWEEN(1,COUNTA(INDEX(C$2:F$21,MATCH(I2,B$2:B$21,0),0))),1)))

    Select H2:J2 and copy down to H11:J11.

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

    Re: Random Lists with no Repeats

    P.S.

    You had duplicate entries for Soup so I replaced one with Kraut and Chops (Yummmmm!).

  12. #12
    Registered User
    Join Date
    01-11-2017
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    8

    Re: Random Lists with no Repeats

    I've made a workbook and tried to duplicate the findings but it has come up short, I have attached it. It seems to be replicating which I do not want it o do and whenever item 1 BBQ is selected it registers a 0 and defaults to chicken. Thanks for looking into this for me.
    Attached Files Attached Files

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: Random Lists with no Repeats

    Here's the file that I worked on. I put the list of meals on a separate sheet, so you can print them out more easily (if you want to).

    Hope this helps.

    Pete
    Attached Files Attached Files

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

    Re: Random Lists with no Repeats

    You had a couple of cell references incorrect. One in the formula in column J and one in the formula in column L.

    Here's your file with the corrections made.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-11-2017
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    8

    Re: Random Lists with no Repeats

    Nice one! Thank you everyone who helped me get this going. I really appreciate the help!

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

    Re: Random Lists with no Repeats

    You're welcome. We appreciate the feedback!

+ 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 Number with No Repeats
    By Hannah122392 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-11-2016, 01:56 PM
  2. VBA random numbers NO repeats
    By jamiegfinch in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-18-2015, 12:01 AM
  3. Replies: 1
    Last Post: 06-14-2013, 04:06 PM
  4. Record Repeats of random function
    By gutkinma in forum Excel General
    Replies: 4
    Last Post: 09-09-2011, 02:19 AM
  5. Excel 2007 : Random seating chart no repeats
    By jhw69 in forum Excel General
    Replies: 1
    Last Post: 09-04-2011, 02:06 PM
  6. Excel 2007 : Random number generator without repeats
    By HRJames in forum Excel General
    Replies: 4
    Last Post: 03-24-2011, 11:37 AM
  7. selecting and summarising random repeats
    By tghcogo in forum Excel General
    Replies: 10
    Last Post: 02-06-2011, 08:31 AM

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