+ Reply to Thread
Results 1 to 5 of 5

count unique values in columns based on date values

  1. #1
    Registered User
    Join Date
    07-26-2010
    Location
    franklin, tn
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    3

    count unique values in columns based on date values

    I manage a call center with staff levels that fluctuate from 12 to 120 agents. I have a workbook that allows me to manage current and upcoming projects. I use the countunique function to give me a headcount based on names entered into a named range "enrollers3" Basic project information such as start / end date are in the rows above my named range.

    I am trying to forecast headcount based on project start / end dates. Agents may be assigned to multiple projects simultaneously or be listed on a current project as well ones with future start dates.

    I have several macros which allow me to sort projects based on criteria in the cell values above my named range. I want a summary sheet which in column a has date values and then in column b the unique headcount for that particular day. As projects are added / dates changed / staff deleted or assigned, I need to be able to reference my summary sheet and see how many unique heads will be in the call center on that day.

    Much obliged for any assistance. I am beating my head against the wall with this one.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: count unique values in columns based on date values

    This struck me as an interesting challenge....

    First, I would make a fairly fundamental point... given the complexity of the model and the calculations you should really be looking to avoid Volatility as the performance impact will be significant.

    For ex. on 7.6 sheet you are (understandably) using:

    Please Login or Register  to view this content.
    in cell B16... however this means that every other cell/calculation that utilises this value (via today range) is also Volatile thereby increasing the Calculation overhead in your model.

    To avoid this I would suggest you use the VBA workbook open event to update this date value with a static value thereby removing all of this Volatility, eg:

    Please Login or Register  to view this content.
    (above would reside in ThisWorkbook).

    The next point I would make concerns your named ranges - which are presently inconsistent in terms of:

    a) their scope (workbook / worksheet)

    b) their dimensions (column references)

    For the purposes of this exercise alone I would probably advise the following names:

    Please Login or Register  to view this content.
    You can then in theory use a UDF to do what you want - which given the Volatility is now removed should not perform too badly

    Please Login or Register  to view this content.
    The above would be called from a cell along the lines of:

    Please Login or Register  to view this content.
    If you wanted to you could modify the above to return both count and the names included in the count

    The above calculation is returning the count of unique names that are active on any given day - not the count of names.

    It is assumed that if the project start date prececeds the date criteria and that the "left" value is something other than "Closed" then the project can be deemed to be active.

    I hope that helps - and I'm sure it can be improved upon.

    To reiterate though - if you implement the above without first removing the volatility your file will perform very badly in Auto Calc mode.

  3. #3
    Registered User
    Join Date
    07-26-2010
    Location
    franklin, tn
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    3

    Lightbulb Re: count unique values in columns based on date values

    Thank you so much for your prompt response. I implemented your suggestions (I'll blame the sloppy named ranges on the fact that this workbook was cobbled together in haste and I have tried numerous attempts to figure the problem out) and the code did a great job in excluding the non active projects.

    The only problem was it didn't want to exclude what are currently upcoming projects when their scheduled closed date had passed at some point in the future. I believe it was caused by this line of code:

    HTML Code: 
    If rngStart(lngCol) <= rngDate And UCase(rngLeft(lngCol)) <> "CLOSED" Then
    Which basically said if the start date is less than or = to the date value in the adjacent cell and the value in the range "left" does not = closed then count the unique heads. Since the formula that drives the value in "left" uses today's date from b16, it treated upcoming cases as if they lasted perpetually.

    I added a named range called "end" and modified your code as follows:

    HTML Code: 
    If rngStart(lngCol) <= rngDate And rngend(lngCol) >= rngDate Or UCase(rngLeft(lngCol)) = "ONGOING" Then
    Which in English I think means "If the start date is less than or = to the date in the adjacent cell and the end date is greater than or = to the date in the adjacent cell or the case is ongoing then count the unique heads.

    I sorted the columns to make testing this a little easier:
    Columns C through J represent perpetual or ongoing cases that have no end date. 19 values, but only 11 that are unique to the worksheet.

    Columns K through O represent cases that are closed now and have 10 values, 9 of which are unique to the worksheet.

    Columns P through T represent upcoming cases and have 10 values, 8 of which are unique to the worksheet.

    So in all there are 29 unique names on the sheet, but for the month of September we max out at 19 unique heads at any given time.

    I added a second a column on the summary sheet which uses the modified code to compare it to the original. Please point out anything I may be missing or room for improving efficiency.

    I can't thank you enough, this will bring me addoration and accolades at work.
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: count unique values in columns based on date values

    The logic seems sound enough to me - apologies for the oversight on my part regards the perpetual project issue.

    I would be wary of using the UDF en masse simply because it isn't a particularly light weight function given use of Dictionary Object.

    You should be aware that whenever any of the precedents are altered - ie:

    Please Login or Register  to view this content.
    every single cell utilising the UDF will be flagged as requiring recalculation.

    It follows that on Auto Calc the calculation time will be significant should you have lots of calls to the UDF.

    You will notice this lag when opening the file -- given the "today" cell is updated this in turn affects the "end" range and therefore flags the UDF's as requiring a recalc.
    The above is both expected & correct (the UDFs do require recalculation as and when that value changes), however, it is just something to be aware of.

    If you find you want to leave the file on Auto Calc but wish to control as and when the UDFs all recalculate you can add a pre-emptive IF to each UDF call based on say a "flag".

    Using your sample file - let's assume:

    -- UDFs in Col B have been removed (legacy UDF)
    -- E1 on Summary sheet contains either 0 or 1 where 1 means UDFs should calculate and 0 means they should not.
    We can modify the in-cell call to:

    Please Login or Register  to view this content.
    We will now find that if we set E1 to 0 and subsequently modify the precedent ranges the UDFs do not recalculate even if we're running Excel in Auto Calculation mode.

    If we then change E1 to 1 from 0 then the UDFs will all recalculate.

    Not an ideal setup but pending the frequency with which you modify the precedent ranges (I'm thinking specifically of enrollers3) you might find it's worthwhile implementing.
    In reality the viability of the above all rather depends on the workflow down the line and how critical these numbers are in the overall operation of the model.

  5. #5
    Registered User
    Join Date
    07-26-2010
    Location
    franklin, tn
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    3

    Re: count unique values in columns based on date values

    Working like a charm. I reduced the # of dates on the summary sheet to look 90 days into the future, and I can probably get by with 60. So far no appreciable problems with performance. Immensely helpful solution to what was a challenging problem. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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