Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Vlookup For Multiple Events

    I looking for suggestions about how to retrieve multiple occurrence events within a column of data utilizing Excel 2010. Specifically, I am trying to record the date and time from one column that are associated with off line and online occurrences recorded in another column. I am stuck on finding a way to continue after either of these events onto the next event. I started out with Vlookup to perform this and, after additional research, have been utilizing the Index Match functions - which helped since the date function defaults as the first column. However, I am still running into the same issue regardless of which one I use. I am open to any and all suggestions. It could be that tenacity is causing me to overlook something simple. Please help. I have attached a sample sheet depicting what I am trying to accomplish along with different attempts in cells E5, C7, D7, and C8 which have all failed for unknown reasons.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is the C7 Off Line the same as the On Line found and shown in C5?

    If so, then what is the (new) Off line in C8 supposed to be?

  3. #3
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I apologize for the lack of clarity. When working correctly, C7 should reflect the second off line date and time which is 04/01/16 @ 08:39 (cell C544). C8 should reflect the second on line date and time which is 04/01/16 @ 17:31 (cell C1076). C10 should reflect the third off line date and time which is 04/01/16 @ 17:58 (cell C1103). C11 should reflect the third on line date and time which is 04/03/16 @ 00:49 (cell C2954). There will be off line and on line headings that do not have any information in them at times. For example, there is only enough off line and on line events in this sample to complete down to cell C11 The others are place holders in case there are that many events during an actual period (this will be set up one month in the end). If there is a way to make only the right amount appear that would be an added benefit. Thank you for your quick response and your assistance.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I don't know...this is pretty ugly, but I think it works.

    There must be a better way, but it's too late for me to think of one.

    When more rows are added to the B and C columns, you'd have to change the 2978 in my formulas to the last row.
    I could have calculated that, but was too lazy to do that.

    I think if you have add'l rows then, you can copy C10 and C11 to C13 and C14 and it should work.
    Attached Files Attached Files

  5. The Following User Says Thank You to kweaver For This Useful Post:

    workingonit (2016-04-22)

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Essentially, you are detecting when something switches from Off Line to On Line.
    So, in my attached file, we just add a formula to detect whether a 'switch' has occurred or not.
    We can use this formula to 'increment' whenever a changeover is detected.
    Then, we can use s simple Index/Match to fetch when this occurred.

    I added an IFERRROR, to return a blank, if say, the nth switchover hasn't occurred yet.

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    workingonit (2016-04-22)

  8. #6
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you kweaver. This will work great. My plan is to set this up for querying one month at a time so I believe that I can adjust the 2978 to 44665 ((31*24*60)+25). It looks like I have some additional functions to learn about. I am also puzzled with the 24 + match but I can continue by bisecting your formula to learn why it is needed. Thank you again.

  9. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Good move, Zeddy.

    Workingonit, the 24 offset because your data begins in row 25.

Posting Permissions

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