+ Reply to Thread
Results 1 to 11 of 11

Efficient drop down lists (without blanks)

  1. #1
    Registered User
    Join Date
    10-19-2017
    Location
    Israel
    MS-Off Ver
    2016
    Posts
    5

    Efficient drop down lists (without blanks)

    Hello,
    I'm building this program for creating monthly rosters for a hospital department.
    What it basically does is for each type of shift, it creates a table that contains only names of workers who are available for
    this shift on the particular day (based on requests,quotas and previous assignments). This data is then displayed in drop down lists where the user can assign the workers.

    These tables naturally have many blank cell, and it makes the drop down lists rather hard to use. So I found this formula that allows me to put
    all of the names in consecutive cells (I took the formula from this video https://www.youtube.com/watch?v=cMchVJe7TDs&t=449s and changed it to work for rows instand of
    columns). Now there are over 20 of these tables (each one has 31 rows for days and around 160 columns for the worker's names) and when I use this formula I'm
    actually doubling the whole thing. It turns out that by doing so I am severely slowing down the program (I know that the problem is this specific formula because I've built this
    program before without removing the blanks and it worked smoothly).

    I've built this simple version of the program because I thought it would be too clumsy to explain in words how it works exactly. (it has 10 days insteadof 31, 6 types of shifts instead 25, 6 workers instead of 160. Additionally, I've made only one functioning type of shift table and it is the only type of shift you can assign in the main table, but it should explain the concept).

    My question is how can I get the same function more efficiently so it won't be so slow? (The attached program works smoothly of course because it is on a very small scale)
    Is there a more efficient formula for removing blank space? or maybe there is a better approach for the whole thing all altogether? (I should mention that the "main table" should
    remain more or less the same, preferably without combo boxes of any sort)

    Thanks in advance and if the way the program works isn't clear enough please let me know.
    Attached Files Attached Files
    Last edited by Sergey_123; 10-25-2017 at 03:23 PM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Efficient drop down lists (without blanks)

    Hi there,

    Take a look at the attached workbook and see what you think.

    It uses a VBA routine (rather than formulas) to populate the "Names of workers (without blanks)" table. It's not possible to tell how this will scale up to the real-life version of your workbook, but my feeling is that it should be more efficient than using the current (fairly processor-intensive!) formulas. Enter a name in the Main Table and then click on the "Update Dropdown Lists" button to see the result. In the final version, this updating could be performed automatically in response to any change in the Main Table.

    As far as extending the demonstration version to the real-life version is concerned, it shouldn't be too involved - more defined-name ranges will be required on the main worksheet, and a looping routine will be required to apply the updating routine to all of the Shift tables. This can be implemented without too much difficulty. I can help you with this if you feel it's worthwhile proceeding along this route.

    Hope this is useful/helpful - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Efficient drop down lists (without blanks)

    Hi again,

    The attached workbook illustrates a different approach which I feel may be more efficient than my previous version.

    The "Names of workers (without blanks)" table is eliminated, and the various dropdown lists for data validation are generated (from the "Names of workers" table) and are assigned automatically to the various cells in the "Main" table. An added mini-bonus is that the dropdown lists no longer include blank values at the ends of the lists.

    Do you want to continue investigating the possibility of creating the dropdown lists in this way? Please keep me informed.

    Regards,

    Greg M
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Efficient drop down lists (without blanks)

    Instead of a static range as the source of the validation use this as the source for the drop down in D6 and fill down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will give you the ranges dynamically for each drop down going down column D 6-15 excluding the blanks

  5. #5
    Registered User
    Join Date
    10-19-2017
    Location
    Israel
    MS-Off Ver
    2016
    Posts
    5

    Re: Efficient drop down lists (without blanks)

    Hi again,

    The attached workbook illustrates a different approach which I feel may be more efficient than my previous version.

    Greg M
    Hi!
    First of all, thank you for the detailed answer, I appreciate it very much.
    I think the second method of skipping the "Names of workers (without blanks)" might prove to be more efficient. I have a pretty basic understanding of VBA so I need some time to learn the code (first thing in the morning). Do you think it will have a nagtive effect on the run time if the updating of the drop down lists will occur as an event (After a change in a relevant cell)?
    Last edited by AliGW; 10-21-2017 at 02:27 AM. Reason: Quotation shortened.

  6. #6
    Registered User
    Join Date
    10-19-2017
    Location
    Israel
    MS-Off Ver
    2016
    Posts
    5

    Re: Efficient drop down lists (without blanks)

    Quote Originally Posted by Zer0Cool View Post
    Instead of a static range as the source of the validation use this as the source for the drop down in D6 and fill down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hey,

    Thank you for the response. This formula does make the drop down lists much neater, but I'm afraid that it won't improve the speed of the program since the
    "Names of workers (without blanks)" tables will be updated each time a change is made (And I think that this is what causing the decrease in performance)
    Last edited by AliGW; 10-21-2017 at 02:29 AM. Reason: Quotation shortened.

  7. #7
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Efficient drop down lists (without blanks)

    The performance issue you are experiencing is a whole other story.

    A macro will never be as fast as a built in function or set of functions. VBA is single threaded whereas formulas are calculated in a threaded manner.

    The speed is either reasonable considering what you are asking of Excel on the given hardware or its not reasonable given the number of calculations etc due to some other factor. So for example if your file is 100,000 rows or its 10MB and it takes 1 min to calculate, thats likely reasonable considering the data set and formulas being used. If your file is 20 rows or 100KB and it takes 15 mins to calculate, theres an obvious issue that needs attention. Can you give us an idea of the size of the file, number of sheets, rows/columns total, etc.?

    Does the sample you posted have all of the formulas in it you use in your actual file?
    Last edited by AliGW; 10-21-2017 at 02:28 AM. Reason: Unnecessary quotation removed.

  8. #8
    Registered User
    Join Date
    10-19-2017
    Location
    Israel
    MS-Off Ver
    2016
    Posts
    5

    Re: Efficient drop down lists (without blanks)

    The first time I built this sort of program (without removing the blanks) the file size was 500KB, it had around 900 rows and 70 columns, most of it were tables like "Shift 1 Table" (20 tables, each one has 30 rows and 160 columns). It had few more formulas but non of them were used frequently (only for publishing the document etc).
    This time I spread the main table, request table, quota table and the shift tables across 4 sheets, and after creating only 4 of the shift tables without blanks (out of 20), the program ran three
    times slower than the previous version. Meaning that instead of waiting no time at all after each name assignment, I have to wait around 3 seconds. That's why I'm pretty sure eliminating the blanks is the only problem..

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Efficient drop down lists (without blanks)

    Hi again,


    A macro will never be as fast as a built in function or set of functions. VBA is single threaded whereas formulas are calculated in a threaded manner.

    I'm quite prepared to accept that using built-in functions directly in a worksheet will always be more efficient than a "do-it-yourself" VBA implementation of those same functions, but that's NOT what we're talking about here.

    Each of the 160x31 cells in each of the 25 "Names of workers (without blanks)" tables requires a significant (nine combined functions) amount of calculation. The proposed approach simply scans the equivalent cells in the "Names of workers" tables, tests them for non-blank values, concatenates any non-blank values (with separators) and assigns the result to a string variable which is then used to create the data validation list for the appropriate cell in the "Main" table.

    From a construction and maintenance point-of-view, this approach is probably better because it relies on the fact that the "Names of workers" tables will always have the same dimensions as the "Main" table, but does not use calculations based on the relative positions of these tables on the worksheet. This is likely to make the worksheet slightly more flexible/robust in the event that worksheet layout changes are necessary in the future.

    If you want to post the 500kB version of your workbook here I'll take a look at it, or you can email it to me if you'd rather not post it. Let me know, and I can send you my email address in a private message if you want.

    The project seems a very interesting one, so I'd be interested in helping out with it.

    Regards,

    Greg M



    P. S. At this stage it's difficult to predict what the execution time will be when this approach is extended to the "real-life" version of the workbook, but my feeling is that triggering the updating routine from the "Worksheet_Change" event shouldn't introduce unacceptably long delays.



    P. P. S. Many thanks for the Reputation increase - much appreciated! .
    Last edited by Greg M; 10-21-2017 at 08:19 AM. Reason: PS. & PPS. added

  10. #10
    Registered User
    Join Date
    10-19-2017
    Location
    Israel
    MS-Off Ver
    2016
    Posts
    5

    Re: Efficient drop down lists (without blanks)

    Hi Greg,
    I sent you couple of messages (I'm not sure they arrived because they don't seem to appear in the "sent items").

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Efficient drop down lists (without blanks)

    Hi again Sergey,

    Messages received & reply sent - many thanks.

    Regards,

    Greg M

+ 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: 11
    Last Post: 10-08-2021, 04:55 AM
  2. [SOLVED] data validation lists - drop down lists too small
    By reglook0736 in forum Excel General
    Replies: 3
    Last Post: 04-24-2015, 12:49 PM
  3. Dependent drop down lists without creating unique named lists
    By pajordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:20 PM
  4. Lists eliminating the blanks
    By mjhopler in forum Excel General
    Replies: 2
    Last Post: 05-27-2011, 02:17 PM
  5. Replies: 5
    Last Post: 05-21-2010, 04:34 PM
  6. drop down lists referencing tables creating other drop down lists!!
    By Stumped- in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 08-07-2009, 11:29 AM
  7. Lists, how to make drop-down lists optional
    By HarvardMajesty in forum Excel General
    Replies: 2
    Last Post: 05-13-2009, 10:23 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1