+ Reply to Thread
Results 1 to 4 of 4

Random selection from remaining items, volatile manual adjustment

  1. 07-02-2021, 02:20 AM #1
    Marbleking
    Marbleking is offline
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Random selection from remaining items, volatile manual adjustment

    Hi,

    I am looking to improve the efficiency of the VBA code and Excel formulas used for the attached workbook. The workbook works according to the algorithm listed below, using a combination of VBA code and Excel formulas. It would be great if anyone could improve on this solution. Right now it is taking quite some time to complete a cycle, and seems to get slower the fewer remaining non-evaluated items there are left.

    1. Click «Chord Lottery» button in Sheet1 (Chord lottery).

    2. If the table in Sheet1 (Chord lottery) has been pre-populated (i.e. if B8:B11, F8:F11 and J8:J11 and their dependents have been populated), then:

    3. If for any of the three sections, B5:E5 = B6:E6, F5:I5 = F6:I6 and J5:M5 = J6:M6 of Sheet1 (Chord lottery), respectively, then for those sections:

    4. Copy data from C8:E8, G8:I8 and J8:M8 of Sheet1 (Chord lottery), respectively to columns A:C in Sheet2 (Chord sequences) according to the row numbers given in B8, F8 and J8 of Sheet1 (Chord lottery).

    5. If for any of the three sections, B5:E5 <> B6:E6, F5:I5 <> F6:I6 and J5:M5 <> J6:M6 of Sheet1 (Chord lottery), respectively, then for those sections:

    6. Copy data from C8:E8 and C9:E9, G8:I8 and G9:I9, J8:M8 and J9:M9 of Sheet1 (Chord lottery), respectively to columns A:C in Sheet2 (Chord sequences) according to the row numbers given in B8 and B10, F8 and F10, J8 and J10 of Sheet1 (Chord lottery).

    7. When the above steps have been performed, or if the table has not been pre-populated as described in item #2, or if the table contains errors, then:

    8. Get three random row numbers between 2 and the used number of rows in column H of Sheet2 (Chord sequences), and enter those numbers in B8, F8 and J8 of Sheet1 (Chord lottery), respectively. NB! Choose only from rows where column A, B and/or C haven't already been populated with data!

    9. Get three random column numbers from the list 4 + (5 * [randbetween(1,12)]), which represents the columns 9, 14, 19, 24, 29, 34, 39, 44, 49, 54, 59, 64 of Sheet2 (Chord Sequences), and enter those numbers in B9, F9 and J9 of Sheet1 (Chord lottery), respectively.

    10. Based on the row numbers in item #8 above, get data from respective rows of column D:G of Sheet2 (Chord sequences), and paste those data in B3:E3, F3:I3 and J3:M3 in Sheet1 (Chord lottery), respectively.

    11. Based on the row numbers in item #8 above and the column numbers in item #9 above, get data from respective rows of and the four consecutive columns counting from each column number in item #9 of Sheet2 (Chord sequences), and paste those data in B5:E5, F5:I5 and J5:M5, respectively.

    12. Copy data from B5:E5, F5:I5 and J5:M5, to B6:E6, F6:I6 and J6:M6 in Sheet1 (Chord lottery), respectively.

    13. Match the entries of cells B6, F6 and J6 in Sheet1 (Chord lottery), respectively, with the content in the columns 9, 14, 19, 24, 29, 34, 39, 44, 49, 54, 59, 64 of Sheet2 (Chord Sequences) (4 + (5 * [n1 … n12]]), then return the column number where a match is found to cells B11, F11 and J11 of Sheet1 (Chord lottery), respectively.

    14. Match the entries of B6:E6, F6:I6 and J6:M6 in Sheet1 (Chord lottery), respectively, with the content of the four consecutive columns in Sheet2 (Chord Sequences), counting from the column numbers given in item #13 (cells B11, F11, J11 in Sheet1 (Chord lottery)), respectively, and return the row numbers where matches are found to cells B10, F10 and J10 in Sheet1 (Chord lottery), respectively.

    15. Get data from columns D:G of Sheet2 (Chord Sequences) according to the row numbers given in item #14 above (cells B10, F10 and J10 in Sheet1 (Chord lottery), respectively), and paste those data to B4:E4, F4:I4 and J4:M4 in Sheet1 (Chord lottery).

    16. Enter 0 in C8, G8 and K8 in Sheet1 (Chord lottery).

    17. Copy the entry of B5, F5 and J5 to D8, H8 and L8 in Sheet1 (Chord lottery), respectively.

    18. Enter N/A in E8, I8 and M8 in Sheet1 (Chord lottery).

    19. Enter 0 in C10, G10 and K10 in Sheet1 (Chord lottery).

    20. Copy the entry of B5, F5 and J5 to D10, H10 and L10 in Sheet1 (Chord lottery), respectively.

    21. Enter N/A in E10, I10 and M10 in Sheet1 (Chord lottery).

    22. B6:E6, F6:I6, J6:M6, C8, C10, E8, E10, G8, G10, I8, I10, K8, J10, M8, M10 will all contain dropdown menus for manual replacement of existing data at any time (see data validation tables in columns Q:W in Sheet1 (Chord lottery)).

    23. Upon any manual replacement by selection from a dropdown menu in B6:E6, F6:I6, J6:M6 of Sheet1 (Chord lottery), automatically update the dependent cells B11, F11, J11 and B10, F10, J10 and D10, H10, L10 and B4:E4, F4:I4, J4:M4 in Sheet1 (Chord lottery).

    24. Click «Chord Lottery» button in Sheet1 (Chord lottery): Rinse & repeat.


    Best regards,
    Marbleking
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-03-2021, 06:12 AM #2
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: Random selection from remaining items, volatile manual adjustment

    I did a minor ( not Aminor ) modification in

    Please Login or Register  to view this content.
    on my computer it takes now always ca. 0.2 second to run main procedure doesn't matter if there are 995 or 5 empty rows in Chord_sequences sheet.

    And from the statistical point of view we did no change, as always the probability to select given particular empty cell is equal to 1/number_of_empty_cells
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  3. 07-04-2021, 09:28 AM #3
    Marbleking
    Marbleking is offline
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Random selection from remaining items, volatile manual adjustment

    Thanks a lot for this, Kaper! That's "A minor" modification for the code, "A major" improvement for the program.
    Reply With QuoteRegister To Reply
  4. 07-04-2021, 12:24 PM #4
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: Random selection from remaining items, volatile manual adjustment

    Glad to hear that it improved the performance.

    The next (small) improvement would be if you add at the beginning of main procedure:
    Please Login or Register  to view this content.
    and just before end sub 

    Please Login or Register  to view this content.
    If you are already satisfied with the performance improvement, it would be lovely if you select Thread Tools from the menu link above the first post in this thread and mark this thread as SOLVED.

    Also, as a person who registered almost 10 years ago, so before changes in the forum engine, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Growing Pivot Table - Outside Cell References Constant Manual Adjustment
    By Sharp2k in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-02-2020, 05:24 PM
  2. How to generate a random number list from 1 to 10 with no dupes or volatile functions
    By JulianS96 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-05-2020, 12:29 PM
  3. [SOLVED] Macro to delete certain items then count remaining items #2
    By elgato74 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-21-2019, 02:26 PM
  4. [SOLVED] Macro to delete certain items then count remaining items
    By elgato74 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-14-2019, 03:20 PM
  5. Random selection option, same 'random' selection in two columns
    By applebanana in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2016, 06:16 PM
  6. Hey, every genius, How to avoid duplicate items in random selection from data?
    By bryansky in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-23-2013, 11:19 PM
  7. Random Selection of items in Excel?
    By mnpremo in forum Excel General
    Replies: 8
    Last Post: 09-23-2005, 04:05 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1