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...
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.