+ Reply to Thread
Results 1 to 4 of 4

closest match from multiple criteria

  1. #1
    Registered User
    Join Date
    07-29-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    2

    closest match from multiple criteria

    Hi guys


    I am having a problem with creating a formula for a timekeeping report that I need to accomplish.

    I have four columns in total. column A contains employee ID number, column B contains log entry dates, Column C contains log entry times, and Column D contains schedules.


    A B C D

    ID Log date Log time Schedule
    12345 June 1 2010 5:49 21:00
    12345 June 1 2010 6:05 21:00
    12345 June 1 2010 20:35 21:00
    12345 June 1 2010 21:55 21:00
    12345 June 1 2010 23:32 21:00
    12345 June 2 2010 3:38 21:00
    12345 June 2 2010 6:15 21:00
    12345 June 2 2010 20:15 21:00
    12345 June 2 2010 20:48 21:00
    12345 June 2 2010 21:08 21:00
    12345 June 3 2010 5:22 21:00
    12345 June 3 2010 6:13 21:00
    12345 June 3 2010 20:25 21:00
    12345 June 3 2010 20:58 21:00
    12345 June 3 2010 21:32 21:00
    12346 June 1 2010 12:22 13:00
    12346 June 1 2010 12:45 13:00
    12346 June 1 2010 16:22 13:00
    12346 June 1 2010 19:25 13:00
    12346 June 1 2010 22:12 13:00
    12346 June 2 2010 12:45 13:00
    12346 June 2 2010 15:55 13:00
    12346 June 2 2010 18:25 13:00
    12346 June 2 2010 21:02 13:00
    12346 June 2 2010 22:22 13:00
    12346 June 3 2010 12:56 13:00
    12346 June 3 2010 14:15 13:00
    12346 June 3 2010 16:22 13:00
    12346 June 3 2010 18:04 13:00
    12346 June 3 2010 22:06 13:00


    OK so here's what I want to happen, I want to create a formula in Column E which would calculate the nearest log time based from the employee number, the log date, and the schedule. for example, for employee number 12345 the nearest log time for June 1 should be 20:35. I would then make a conditional format for column C that a cell would be highlighted whenever it has an equivalent value on column E so that when I drag the formula down on column E, specific cells will be highlighted on Column C. these highlighted once will then be considered the first log-in swipe for the day.

    this is an example of what would appear when I drag the formula down

    A B C D E

    ID Log date Log time Schedule First log
    12345 June 1 2010 5:49 21:00 20:35
    12345 June 1 2010 6:05 21:00 20:35
    12345 June 1 2010 20:35 21:00 20:35
    12345 June 1 2010 21:55 21:00 20:35
    12345 June 1 2010 23:32 21:00 20:35
    12345 June 2 2010 3:38 21:00 20:48
    12345 June 2 2010 6:15 21:00 20:48
    12345 June 2 2010 20:15 21:00 20:48
    12345 June 2 2010 20:48 21:00 20:48
    12345 June 2 2010 21:08 21:00 20:48


    Also, please take note that if 2 log-in swipes within a day are very close to the schedule (one before schedule and another is after schedule), the formula should recognize the one BEFORE the schedule. this is to prevent confusion that an employee is late even if he is not. One example is June 2 of employee 12345, 21:08 is closer to 21:00 than 20:48 but the formula should recognize 20:48 as the first login.

    I have tried various formulas but to no avail. if someone here could help

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

    Re: closest match from multiple criteria

    Quote Originally Posted by wongja
    Also, please take note that if 2 log-in swipes within a day are very close to the schedule (one before schedule and another is after schedule), the formula should recognize the one BEFORE the schedule
    The fact that you are not in fact looking for the closest match per se makes this calculation less intensive (ie no need for ABS array test).

    If we assume per the sample that the data is sorted by ID, Log Date, and Log Time (in that order) then

    Please Login or Register  to view this content.
    The LOOKUP calc is only performed once per ID (first) - all subsequent instances simply use the first result.
    (you could restrict the LOOKUP range further using embedded INDEXs but I would say they're probably not worthwhile)
    Last edited by DonkeyOte; 07-30-2010 at 01:40 AM.

  3. #3
    Registered User
    Join Date
    07-29-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: closest match from multiple criteria

    Thanks! this formula worked but it returned some unexpected results.


    Since it prioritizes the "before schedule" log than the "after schedule" log, some values returned are way off the expected results.

    can you make it so that it now returns the closest match? I believe I can make a more accurate report by using that

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

    Re: closest match from multiple criteria

    Quote Originally Posted by wongja
    Since it prioritizes the "before schedule" log than the "after schedule" log...
    Yes, per your requirements.

    Quote Originally Posted by wongja
    can you make it so that it now returns the closest match? I believe I can make a more accurate report by using that
    Please Login or Register  to view this content.
    Again, as before, the main calculation is performed only once per combination of ID & date - subsequent records use the previously calculated value - this assumes the data is sorted by ID & date.

    Given the assumption that the data is sorted you can in theory embed some INDEXes in there to reduce the range size being processed in the Array - whether or not that is worthwhile will depend on the size of the original data set.
    Last edited by DonkeyOte; 07-31-2010 at 02:20 AM. Reason: modified quote

+ 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