Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convaluted Lookup

    Hi,

    I'm looking for some help regarding a rather convaluted lookup - I have a list of people, that during different time periods had different charge out rates. I now have to find out the rates for particular dates...

    The attached example may help make a bit more sense of this issue....I have a table of data that defines each time period and rate for each person, and I need to be able to calculate out what rate each person has on a specific date. Of course this is an over simplified example, so whatever the approach has to be readily scalable!

    Any suggestions on how to approach this? Thanks in anticipation

    Alba.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alba,

    Here's a UDF {User Defined Function} that will accomplish the task. This is a quick first take and it can probablly be tweaked for greater efficiency.

    Code:
    Option Explicit
    
    Function iRate() As Integer
     
    'Uses the following Range Names:
    '  RateTable  defined as the entire table EXCLUDING the header row.
    '  UpperRight defined as the 1st person in the RateTable
    
    'Calling Sequence: =iRate()
      Dim iCntr   As Integer
      Dim iTblLen As Integer
      Dim zCaller As String
      
      
      iTblLen = Range("RateTable").Rows.Count - 1
      
      zCaller = Application.Caller.Address
      For iCntr = 0 To iTblLen
      
         If Range(zCaller).Offset(0, -2).Value = Range("UpperRight").Offset(iCntr, 0).Value Then
         
           If Range(zCaller).Offset(0, -1).Value >= Range("UpperRight").Offset(iCntr, 1).Value And _
              Range(zCaller).Offset(0, -1).Value <= Range("UpperRight").Offset(iCntr, 2).Value Then
              iRate = Range("UpperRight").Offset(iCntr, 3).Value
              Exit Function
           End If
           
         End If
         
         iRate = 0   'No match found
      
      Next iCntr
      
    End Function    'iRate()
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Very neat indeed! I should be able to scale this up nicely to work with the data I have - thanks very much for your help with this, I would never have got there without your help!

    Alba

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alba,

    You're welcome.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I'm just working through how to modify this to suit my data - is there a way that I can do the date range lookup based upon the date in the Start column only - ie perform the comparison between the cells vertically with the assumption that the last date for each person has the end date of present day?

    ??

    Alba

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alba,

    I'm not exactly sure what you have in mind. One thing you could do is to set the last entry's End date for the individual to =Now() and it will work but if that's not what your after please post an example of your lists.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not able to post an example just now, but what I mean is the removal of the 'end date' column. So the end date for a rate would become the cell immediately underneath. I'll pop an example up in a couple of hours..Thanks!!

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alba,

    Ok, here's a version that only uses one column. These assumptions apply:
    1. The date of a rate is the date it STARTS.
    2. The previous rate ends o the date before the next one starts.
    3. The last entry for each person still used the =Now() as the Date and leaves the rate blank. You can enter the next rate by inserting above the line with the =Now() entry and everything will be preserved, e.g. the Range Names.

    It is possible to eliminate the =Now() entry but then the logic for the UDF becomes much more involved.
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks very much Geek! The data does not contain a now() entry, but with a wee bit of work, it can. You are an absolute STAR!!

    Alba

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •