Return multiple results for a vlookup using the range "TRUE" argument

[Lazarus7]

HI all,

I'm having an issue returning multiple results for a vlookup I'm using to tell me all instances that have occurred for a certain time period.

What I have is a certain point in time in one sheet that I need to know all the downtime events that were occurring at that point in time. My data I'm looking at is in sequential order and has the downtime events occurring with a start and end time.

Currently I'm using this formula =VLOOKUP(B2,'A1 Downtime'!$E$4:$G$113,3,TRUE) where B2 is the point in time I am looking at, column E has the start time of the event, column F the end time of the event & column G the description of the event. I've converted my date & times into numbers.

At the moment I am getting the first result but on quite a few occasions there is actually more than one downtime event occurring at that single point in time, so I want to return all events that are happening at that time.

Sorry for the long winded post, any ideas?

Thanks

 

[mart37]

Use AUTO FILTER

 

[oldbrewer]

[TABLE="width: 768"]
<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]time[/TD]
[TD="width: 64"]event[/TD]
[TD="width: 64"]helper[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl22, align: right"]09:00:00[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, align: right"]09:30:00[/TD]
[TD]E2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, align: right"]10:00:00[/TD]
[TD]E3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, align: right"]10:00:00[/TD]
[TD]E4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, align: right"]11:00:00[/TD]
[TD]E5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, align: right"]11:30:00[/TD]
[TD]E6[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="colspan: 6"]so in a nutshell you want to find E3 and E4 AND E6,E7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22, align: right"]11:30:00[/TD]
[TD]E7[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]E3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]E6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]E7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]the helper finds identical times[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]and offset match is used to pull the events[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Lazarus7]

Not sure that either of those has helped me, mart37 I'm not sure how autofilter will help? I have approx 400 30 minute time intervals per month that I need to return events for, I think autofilter will only help in a single instance.

oldbrewer how does the helper work, I don't understand from what you have posted?

 

[Lazarus7]

Problem I'm not sure how best to articulate

I put up a thread the other day but I didn't really say very well what I was trying to do, so I'm having another try with more detail below.

I have two lists of data, one has a bunch of 30 minute intervals over a month (around 300) which I need to provide comments for. The format on that is:
[TABLE="width: 257"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]INTERVAL_START[/TD]
[TD]INTERVAL_END[/TD]
[/TR]
[TR]
[TD="align: right"]1/09/2017 8:00
[/TD]
[TD="align: right"]1/09/2017 8:30[/TD]
[/TR]
</tbody>[/TABLE]
The second sheet I have has downtime reasons for that month and has abut 1000 rows and they are in this format:
[TABLE="width: 272"]
<colgroup><col><col><col><col></colgroup><tbody>[TABLE="width: 474"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date Off[/TD]
[TD]Time Off[/TD]
[TD]Date On[/TD]
[TD]Time On[/TD]
[TD]Event Description[/TD]
[/TR]
[TR]
[TD="align: right"]1/09/2017[/TD]
[TD="align: right"]1:56:00[/TD]
[TD="align: right"]1/09/2017[/TD]
[TD="align: right"]2:00:00[/TD]
[TD] Sand Flow Poor
[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]

The tricky bit is that there may have been multiple events occurring during that half hour time period

So what I need to do is return all the events that were underway during each 30 minute time period, whether they began in that interval or were already underway as that interval began, and also if it was underway at the start of that interval but finished before the end of the interval.

So far I've managed to translate all my date & time data into numbers thinking that was the way forward but have hit a wall.

If anyone has any ideas it would be much appreciated.

Thanks

 

[BrianJN1]

Re: Problem I'm not sure how best to articulate

Are you somehow monitoring some internet output to which you have a need to respond?

Sheet 1 has this format for some 300 listings:

[TABLE="class: cms_table, width: 257"]
<tbody>[TR]
[TD]INTERVAL_START[/TD]
[TD]INTERVAL_END[/TD]
[/TR]
[TR]
[TD="align: right"]1/09/2017 8:00[/TD]
[TD="align: right"]1/09/2017 8:30[/TD]
[/TR]
</tbody>[/TABLE]

but I don't see the format for Sheet 2 unless it is the same.

While I personally will be unable to assist I'd advise you to post a brief sample of your file (two sheets) to some cloud sharing location such as DropBox. Then you may be able to explain more succinctly what is to happen.

 

[Lazarus7]

Re: Problem I'm not sure how best to articulate

Sorry the second data set looks like so:

[TABLE="width: 474"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date Off[/TD]
[TD]Time Off
[/TD]
[TD] Date On
[/TD]
[TD]Time On
[/TD]
[TD] Event Description
[/TD]
[/TR]
[TR]
[TD="align: right"]1/09/2017[/TD]
[TD="align: right"]1:56:00[/TD]
[TD="align: right"]1/09/2017[/TD]
[TD="align: right"]2:00:00[/TD]
[TD] Sand Flow Poor
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Lazarus7]

Re: Problem I'm not sure how best to articulate

Here is a dropbox with a sample of my data

https://www.dropbox.com/s/h9q904jwgpsjhil/Excel Sample.xlsx?dl=0

 

[BrianJN1]

Re: Problem I'm not sure how best to articulate

Ok. Your data is fed to your sheets in a way that does not concern us, fine.

Your problem is to take the appropriate reasons from the "Downtime" Sheet and have then appear correctly in Col J of "Periods"?
Is that correct?

If that is so, then that is certainly beyond me but any further guidance may assist another to assist further.
Thank you for that provision.

 

[oldbrewer]

the helper puts a 1 by each identical time - but the crafty thing is - it is not a 1 it is 1 + row()*.00000001

this means you can find each "1" with offset match going 1 to the left to get the event

are you familiar with offset match ?