Vlookup For Multiple Events

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Good move, Zeddy.

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

Reply | Quote