+ Reply to Thread
Results 1 to 6 of 6

Preset randomly distributed numbers that must be oragnised based on several rules.

  1. #1
    Registered User
    Join Date
    04-20-2013
    Location
    europe
    MS-Off Ver
    Excel 2016
    Posts
    12

    Preset randomly distributed numbers that must be oragnised based on several rules.

    Hi excelforum!

    I've tried to solve this for several days, but this goes beyond my knowledge.

    So, the problem.

    I have 50 preset numbers in 2 sets, 25 each. Lets say a1-a25 and b1-b25. Neither of these are sequences, nor repetitions of each other. No number in the "a" set can be found in the "b" one, and no "b" number can be found in the "a" set.

    I want these placed randomly in a 45 row high and 5 column wide area. However since there are 225 cells in the area, the sets must be used more than once.
    Each number from the "a" set must appear 5 times overall, and each number from the "b" set must appear 4 times overall.
    Distribution within a row is not importat, There could be any combination or order of "a" and "b" numbers in the rows.
    What really matters is that numbers cannot reapeat within a single row, and thats where I got stuck. ¯\_(ツ)_/¯

  2. #2
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Preset randomly distributed numbers that must be oragnised based on several rules.

    Interesting problem, even more interesting as to why!

    You say that no number in column a can be in column b and vice versa.

    I assume that no number it column a can be repeated in column a correct? Otherwise you may end up with an impossible solution.

  3. #3
    Registered User
    Join Date
    04-20-2013
    Location
    europe
    MS-Off Ver
    Excel 2016
    Posts
    12

    Re: Preset randomly distributed numbers that must be oragnised based on several rules.

    " You say that no number in column a can be in column b and vice versa. "
    No, thats not what I said. Numbers could and will repeat in columns, they cannot repeat in rows.

    I guess this was confusing part?
    "No number in the "a" set can be found in the "b" one, and no "b" number can be found in the "a" set."
    What I meant by this is that the 2 sets are made of 50 different numbers.

    And it is absolutely possible, I almost completed this, but i cannot fix the no repetition in a single row problem. Of course I can fix those manually and its all good, but I really don't want to.
    I want many of these 225 cell arrays, and I want to do further things with it. So this manual fixing of duplications isn't really a viable solution.

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Preset randomly distributed numbers that must be oragnised based on several rules.

    Here is an empirical / Monte-Carlo type solution. It randomly populates the output columns with the set-A and set-B values and iterates until it finds a solution with no row duplicates.

    To use this workbook, make sure you have macros enabled. Populate your set-A and set-B numbers into A3:B27. Click the "get Solution" button. Your 5 columns by 45 rows of set-A/B numbers without row duplicates will be written to M3:Q47

    Details . . .

    A3:A27 holds the 25 "Set-A" values
    B3:B27 holds the 25 "Set-B" values

    rand() / rank() functions provide a randomly sorted set of unique values in the range 1 to 225 in H3:H227

    The range I3:I227 provides a column index into A3:B27. Col-I indices in the range 1 to 125 will yield a column index of 1 corresponding to the five required sets of group-A items. Col-I indices in the range 126-225 will yield a column index of 2 corresponding to the four required sets of group-B items. In I3 copied down to I227:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The range J3:J227 provides a row index into A3:B27. The 225 Col-J indices are populated with the required 5 repetitions of the 25 group-A items and the required 4 repetitions of the 25 group-B items. In J3 copied down to J227:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula for the five column, 45 row output area first gets the row/col indices in I:J and then uses them to index into the 25 set-A and 25 set-B values. In M3 then copied across and down to Q47:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    As a validation check, cols C and D count how many occurrences of each A-set value and B-set value are present in the five column output area. The A-count must be 5 for each value and the B-count must be 4 for each value if this aspect of the output columns population is valid.

    We're looking for no duplicate values in any of the 45 rows of the output area. This is the part that is done (automatically) by trial and error. Col-R contains a check for row duplicates. In R3 copied down to R47:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This is an array formula and assumes that the A and B set are numeric as opposed to text. Cell R48 contains a count of how many rows have duplicates. It needs to be zero for a valid solution.

    Here's the VBA that basically loops around "calculate" until there are zero row duplicates.
    Please Login or Register  to view this content.
    To run it, first set calculations to manual Ribbon > Formulas > Calculation > Manual. Now click the "Get Solution" button that is located around cell N50. It typically takes a few thousand iterations and a minute or so to find a valid solution.

    The "A solution" worksheet captures one example of a valid solution.

    See the attached workbook. Note that due to the bit of VBA you must have macros enabled.

    I'm not sure whether or not this type of solution works for your needs. Let us know.
    Attached Files Attached Files
    Last edited by GeoffW283; 09-12-2020 at 08:33 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  5. #5
    Registered User
    Join Date
    04-20-2013
    Location
    europe
    MS-Off Ver
    Excel 2016
    Posts
    12

    Re: Preset randomly distributed numbers that must be oragnised based on several rules.

    I've integrated it into my existing spreadsheet and database, works as intended!
    Thank you GeoffW283!

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Preset randomly distributed numbers that must be oragnised based on several rules.

    Glad it was useful. Thanks for the rep

+ 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. Replies: 5
    Last Post: 01-29-2018, 07:57 AM
  2. Generating randomly distributed column values
    By syedaliammar in forum Excel General
    Replies: 3
    Last Post: 05-29-2015, 03:25 PM
  3. [SOLVED] How to change excel's preset chart numbers
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2014, 06:41 PM
  4. [SOLVED] Replace words with numbers based on strict rules
    By xbr088 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 09:22 AM
  5. Create new set of numbers based on conditional rules
    By promo786 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 11-27-2011, 04:25 AM
  6. Need help to convert numbers into preset values
    By christianlie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2010, 04:44 AM
  7. [SOLVED] Can the preset numbers in the column be changed?
    By shewahya in forum Excel General
    Replies: 3
    Last Post: 08-18-2005, 02:05 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