Forum Discussion
Possible conditional formatting solution??
Hi,
I’m working with data that has time intervals, temperature, and other values in the columns. Whenever the temp hits a specific value, I have done conditional formatting highlight it so then I can find the time intervals. When working with data that has tens of thousands of rows, is there a way to skip to or record specific areas of data that are highlighted inconsistently instead of having to keep scrolling on the document? Same for finding columns that have gabs? For example, I need the first highlighted cell time time and then the cell that’s right after the highlighted box if that makes sense. It’s been such a tedious process to do. I feel like a what/if then statement could work?? I attached a photo trying to better explain. I wrote “1762” but I meant to write the cell with value “1752”.
please help!!
Thanks!
6 Replies
=AND(E2<>"",E2<10)In the example i've assumed that this is the rule for conditional formatting.
=FILTER(B2:B18,(E2:E18=""))This is the FILTER function in cell G2 that selects all times if the corresponding cell in column E is blank.
=IF(OR(AND(AND(E2<>"",E2<10)=TRUE,AND(E1<>"",E1<10)=FALSE),AND(NOT(ISBLANK(E2)),AND(E2<>"",E2<10)=FALSE,AND(E1<>"",E1<10)=TRUE)),1,"")This is the formula in cell H2 in the example which returns "1" if the condition is true. You can then select cell H2 and skip down to all cells that contain "1" with ctrl and arrow down.
=FILTER(B2:B18,(E2:E18<>"")*(E2:E18<10)*((E1:E17>=10)+(E1:E17="")))This FILTER function records all times at the beginning of a conditionally formatted range.
I used the conditional formatting and entered the formula =E33:E22354<10, and included the blanks or GAPS as we like to call them. These were highlighted yellow. When I entered the FILTER formula, it worked, but wasn't correct. I tried adjusting it to work, but I couldn't figure it out. I attached a file I'm working on and made changes. The date and time of when it starts (which is the first highlighted data value) is recorded along with the date and time of when it ends, which is right after the last value. This gets confusing because a GAP (blanks) are also recorded, but count as a separate time. For example, in the Info tab attached in the Excel document, the start date and time should be
2022/12/31 23:58:00 The stop time is
2023/01/01 13:38:00 which falls into the GAP (blank cells). This is also recorded and counted as a new row, so the start of the next row in the "info tab" is cell 443 (from the data tab) with the start time of
2023/01/01 13:38:00 and end time of
2023/01/01 13:56:00 which is cell 452 (from the data tab).
Then, the third start time is cell 453 in the data tab with a start time of
2023/01/01 13:58:00 and end time of
05:42:00 01/03/2023 05:42 Also, thank you so much for your help thus far! I really appreciate it!
- Sorry about the delay in getting back to you. I went on vacation.
I didn't use a conditional format, I filtered any number that was less than ten and blanks. I should have highlighted the blanks in yellow too. Do I have to use the conditional format for your formula to work? I tried this formula for the data range because my data starts at E33 and ends at E22354: =IF(OR(AND(AND(E33<>"",E33<10)=TRUE,AND(E32<>"",E32<10)=FALSE),AND(NOT(ISBLANK(E33)),AND(E33<>"",E33<10)=FALSE,AND(E32<>"",E32<10)=TRUE)),1,"") but this did not work.
For the filter formula, I tried adding the dates as well because I need both the dates and times, but "#SPILL!" was returned when using this formula: =FILTER(A33:B22354,(E33:E22354=""))
For example, I'd like both 2022/12/31 23:58:00 to show up with the formula=FILTER(A33:B22354,(E33:E22354=""),"")This FILTER function returns the dates and times from columns A and B.
You can read here why SPILL error occurs - Spill range isn't blank - and how to fix this error.
How to correct a #SPILL! error - Microsoft Support
Conditional formatting isn't required for the formulas to work but since you are working with thousands of rows i'd apply conditional formatting. It's better to visualize the rows with values less than 10 in my opinion.
I didn't filter numbers less than 10 and blanks and the formula returns the expected result. The formula is in cell H33 and it enters "1" in the intended rows.
=IF(OR(AND(AND(E33<>"",E33<10)=TRUE,AND(E32<>"",E32<10)=FALSE),AND(NOT(ISBLANK(E33)),AND(E33<>"",E33<10)=FALSE,AND(E32<>"",E32<10)=TRUE)),1,"")
