Forum Discussion
HELP! Need help filtering or finding xlookup or anyother method of finding specific data ranges
If I have a big dataset and I used conditional formatting to highlight any values from E33:E22354 <10 (including blanks) in yellow, is there a way I could use xlookup or a filter function to find the exact start and stop date/time range for those values? For example, I attached an image showing the highlighted conditional formatting. E33 is where the first highlighted value is, so the start time and date I would need to pull is data from A33 and B33: 2023/01/2 10:28:00. The stop date/time would be E37 because it stops at E36 and I want to include this value and show the full range so the stop date/time I need is from cells A&B37: 2023/01/2 10:36:00.
It becomes tricky because I also need the blank values also known as a GAP, so in this example the start time for where the blanks are is the same as the stop time for A&B37: 2023/01/2 10:36:00 and the stop date/time are cells A&B38: 2023/01/2 10:38:00. The next range would be a GAP in row 39 with a start date/time of 2023/01/2 10:40:00 and end time of 2023/01/2 10:42:00. This continues inconsistently throughout E33:E22354. I only clipped part of the data for example purposes.
The only caveat is I would maybe need to add "+1" for the stop time/date because for the stop time, it's the cell after and not all are highlighted.
Any advice or other solutions would be appreciated. It doesn't have to be xlookup or filter, but I plan to automate this process to make it easier. This took me about 4 hours doing it by hand.
Thanks in advance!
I attached a photo, but also copied and pasted the table on here to make it easier to see, but I couldn't highlight the blank spaces, therefore I simply typed in GAP which is left blank in the excel document if that makes sense:
| E33 | 2023/01/22 | 10:28:00 | 0.000 | 3 | 5 |
| E34 | 2023/01/22 | 10:30:00 | 0.000 | 3 | 5 |
| E35 | 2023/01/22 | 10:32:00 | 0.000 | 3 | 5 |
| E36 | 2023/01/22 | 10:34:00 | 0.000 | 3 | 5 |
| E37 | 2023/01/22 | 10:36:00 | 0.000 | GAP | |
| E38 | 2023/01/22 | 10:38:00 | 0.000 | 3 | 2295 |
| E39 | 2023/01/22 | 10:40:00 | 0.000 | GAP | |
| E40 | 2023/01/22 | 10:42:00 | 0.000 | 105 | 2560 |
| E41 | 2023/01/22 | 10:44:00 | 0.000 | 2560 | 2889 |
| E42 | 2023/01/22 | 10:46:00 | 0.000 | 2709 | 2872 |
| E43 | 2023/01/22 | 10:48:00 | 0.000 | 3 | 2920 |
| E44 | 2023/01/22 | 10:50:00 | 0.000 | 3 | 5 |
| E45 | 2023/01/22 | 10:52:00 | 0.000 | 3 | 5 |
| E46 | 2023/01/22 | 10:54:00 | 0.000 | 3 | 5 |
| E47 | 2023/01/22 | 10:56:00 | 0.000 | 3 | 1752 |
| E48 | 2023/01/22 | 10:58:00 | 0.000 | 1752 | 1979 |
For such model
as variant
=LET( less, value < 10, stop, TOCOL(time / IFNA((less)*(DROP(value,1) >= 10),1), 2), start, TOCOL(time / (less * VSTACK(0, DROP(value, -1) >= 10)), 2), VSTACK({"start","stop"}, HSTACK(start, stop)) )- This is a bit confusing. Would I be putting that formula in the start and stop time column after I create it? Also, I do not believe this would work because the stop time for the first row would be 9:30. It starts at 9:10, but has to include the last value therefore it stops after 9:25 is finished, so I use the 9:30:00 value if that makes sense. The next stop time would be 10:35, and the third would be whatever row is after 11:25
If stop is next greater than 10, it could be
=LET( less, value < 10, more, value >= 10, skipFirst, DROP(value, -1), stop, TOCOL(time / VSTACK(DROP(VSTACK(0, skipFirst < 10) * more, -1), 1), 2), start, TOCOL(time / (less * VSTACK(0, skipFirst > 10)), 2), VSTACK({"start","stop"}, HSTACK(start, stop)) )I assume the last record in the range is also "stop"
Entire formula is entered in E3 here
It generates everything, includes headers. The only is to apply formats.
Hello jazzyelsie,
Try something like this:
=LOOKUP(2,1/(I:I=$N1),F:F)
In Column I you have what you want to look for values
In N1 you have what you want to count < 5
In Column F you have what you want to return, the dates.
It would always be a good thing to include the Column letters and row numbers so that we can see the layout of your worksheet and get you a formula that works for your example!
HTH
- You're right. I should've included the column letters. The left side are the row numbers, but it is shown in the attached Excel file. I will definitely make that clear next time I post, thank you 🙂
For the lookup formula, if I need the stop time of the row right after the conditional format highlights, would I add a 1? I'm not sure if that makes sense.
