How to list dates defined by a data validation process

[Aussiebear]

In the attached workbook a graph shows the Min & Max temps recorded at a location. I am hoping to find a way to list all dates in cell I37 and below where by for three consecutive days the maximum temperature was plus or minus 1 of the selected temp in cell G37. If no dates were found then the cell I37 should show the value "None found". If any dates are found, can the listing be shown as date to date in the cell (eg 2/3/24 to 5/3/24). The data in columns A to E will continue to grow over time.

On a side note the chemical "Formic Pro" has a preferred safe use range of 10° to 25° celcius, and I'm trying to identify periods where I might have been able to apply the product as a method of Varroa Mite control.

Any assistance would be greatly appreciated.

[Paul_Hossler]

1. This is tied to the WS Change event. I couldn't find an easy way to only use WS formulas

2. I changed the DV and applied Custom Number formats; it made it a little easier to talk to the Sub

3, Not sure I totally understood Objective 2, but let me know

Option Explicit


Sub UpdateChart()
    Dim iNumDays As Long, iTemp As Long, i As Long, o As Long
    Dim dateStart As Date, dateEnd As Date
    Dim rTemps As Range
    Dim rowStart As Long, rowEnd As Long
    
    With ActiveSheet
        If .ChartObjects.Count <> 1 Then Exit Sub
        
        Application.EnableEvents = False


        Set rTemps = .Range("A1").CurrentRegion


        iNumDays = .Range("G34").Value
        iTemp = .Range("G37")
        Range(.Range("I37"), .Range("I37").End(xlDown)).ClearContents
        .Range("I37").Value = "None"
        


        With rTemps
            If iNumDays > .Rows.Count Then iNumDays = .Rows.Count - 1
            rowStart = .Cells(.Rows.Count - iNumDays + 1, 1).Row
            rowEnd = .Cells(.Rows.Count, 1).Row
            dateStart = .Cells(rowStart, 1).Value
            dateEnd = .Cells(rowEnd, 1).Value
        End With
            
        o = 37
            
        For i = rowStart To rowEnd - 2
            If Abs(.Cells(i, 2).Value - iTemp) >= 3 And Abs(.Cells(i + 1, 2).Value - iTemp) >= 3 And Abs(.Cells(i + 2, 2).Value - iTemp) >= 3 Then
                .Cells(o, 9).Value = .Cells(i, 1).Value
                o = o + 1
            End If
        Next i
        
        With .ChartObjects(1).Chart
            .Axes(xlCategory).MinimumScale = CDbl(dateStart)
            .Axes(xlCategory).MaximumScale = CLng(dateEnd)


            .ChartTitle.Caption = "Temperature " & dateStart & " - " & dateEnd & Format(iNumDays, " (## days)")
        End With


        Application.EnableEvents = True


    End With


End Sub

[snb]

Use autofilter
I chnaged the validation in G37 to: 20, 21 ,22, 23, 24 ... etc.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$G$37" Then
    Cells(37, 9).CurrentRegion.Offset(1).ClearContents

    With Cells(1).CurrentRegion
       .AutoFilter 2, ">" & Target - 1, xlAnd, "<" & Target + 1
       For Each it In .SpecialCells(12).Areas
         If it.Rows.Count = 3 Then it.Copy Cells(Rows.Count, 10).End(xlUp).Offset(1)
       Next
       .AutoFilter
    End With
  End If
End Sub

[p45cal]

In the attached on sheet Temperature (2):
cell J36, your opportunities list, a lambda formula.
cell K36, list of dates for plotting on the chart.

2024-03-17_202636.jpg

Also, same two lists using Power Query in cells O35, M35. Needs refreshing.

[Paul_Hossler]

Impressive - I can't wrap my head around using the new functions like that. I can use one or two, but I can't nest them like that. (old dog, new tricks )

I don't see the chart updating. I tried re-calculating everything

For example, with "Last 30 Days" i'd expect the x-axis to be from 2/17/2024 to 3/17/2024

Capture.JPG

I don't have (use) Power Query so is there another way?

[Aussiebear]

So far P45cal is on the money. But it raises a number of questions, like;

Should the graph be on a seperate sheet to maintain clarity or can I get away with hiding the working data behind the chart? Which is better for working with?
I really like the highlighting on the chart, of the possible days where the chemical could have been applied. Simply didn't know you could do that.
Will the highlighted days on the chart automatically reflect any changes in the selected temp value?

Please accept my thanks for the efforts made by Paul, snb and P45cal

[p45cal]

I don't see the chart updating. I tried re-calculating everything
For example, with "Last 30 Days" i'd expect the x-axis to be from 2/17/2024 to 3/17/2024

Paul, I didn't address that part, Aussiebear made that a separate thread http://www.vbaexpress.com/forum/show...selected-range
So the only thing which changes on the chart is the added Opportunities series.

I don't have (use) Power Query so is there another way?

I'm guessing you mean that you don't use it, because if the formula works at your end I feel you must have Power Query (Get & Transform Data on the Data tab of the ribbon). The query tables need refreshing in the same way as you would refresh Pivot Tables (after changing the value in cell G37, either right-click each (green) table and choose Refresh, or click Refresh All in the ribbon).
As far as 'is there another way?', well, yes, the formulae in cells J36:K36 (and R36), unless I've misunderstood.

[p45cal]

Will the highlighted days on the chart automatically reflect any changes in the selected temp value?

Yes!
As I left it, the chart uses only the formula solution, so since the formulae update themselves as soon as the source data changes, the chart will update straightaway.
The formulae were not easy to put together (it's likely they're longer than they need to be; it's only what I first got to work while playing around).
The Power Queries were a lot easier to make but, at the moment, they need manually refreshing; this could be addressed with a one or two line macro.
As far as where to put the chart, well that's entirely a matter of taste!

On a separate matter, you're only looking at the max temperatures - wouldn't you prefer to see opportunities when both max and min temperatures are in range (even if you do have to tweak that range a bit if your seasonal temperatures don't allow you to apply the stuff at all)?

[Aussiebear]

On a separate matter, you're only looking at the max temperatures - wouldn't you prefer to see opportunities when both max and min temperatures are in range (even if you do have to tweak that range a bit if your seasonal temperatures don't allow you to apply the stuff at all)?

Yes. Currently I am only looking at the max temps because its late Summer early Autumn here, (just trialling this concept), but you are right, later on when the temps fall the Minimums will be a deciding factor.

[Paul_Hossler]

@p45cal --

I did see the other post, but this one was later and had the 2 objectives in it:

1st objective is to have the graph show the data for the period indicated in cell G34

2nd Objective is to be able to list the dates when for 3 cosecutive days the maximum temps were plus or minus the temperature selected in cell G37 and if any list them in Cell I37 and below, in the format date to date, otherwise show value None in cell I37.

so I combined them.

I prefer (VERY personal choice) to stay with VBA solutions since I find the code easier to follow than something like

=VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,BYROW(R36#,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE))

I can never get the number of parens, etc. to match without a lot of trial and error

And yes I have PQ, but really don't use it, even though I see you using it a lot and it does look like it is powerful, but seems to have a steep learning curve. I will experiment with it a little more since my Excel, etc. is just funnsie since I retired.

I did try Refresh, but it wasn't working. I was a version or two behind .

I'm up to date now (Microsoft® Excel® for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20058) 32-bit) and was able to refresh

BTW, I don't know if it matters but some combanations of data throw errors

Capture.JPG

[Aussiebear]

Your right Paul, as when I changed the temp value to 25, it had a wobbly and threw Calc errors as well.

@P45cal. The following formulas could be secret rocket launch codes for all I know. Just exactly what do they mean, and remember you are speaking with a convict here so go gently with the wording.

=VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,BYROW(R36#,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE))

and

=VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,BYROW(R36#,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),Table1__2[@Date],"¬",TRUE))

and this one is

=LAMBDA(myDates,maxes,celsius,LET(g,SORT(FILTER(myDates,ABS(maxes-celsius)<=1)),f,SORT(g)-SEQUENCE(COUNT(g)), _
e,UNIQUE(f),c,DROP(FREQUENCY(f,e),-1),d,XLOOKUP(e,f,g),b,FILTER(HSTACK(c,d),c>=3),result,TEXTSPLIT(TEXTJOIN("¬",TRUE, _
BYROW(b,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE),b))(A2:A78,B2:B78,Temperature)

and before you get started what the hell is this? "¬". What was it meant to be before it got smashed by a truck?

These formulas probably make Georgiboy go weak at the knees with excitement!

[Paul_Hossler]

These formulas probably make Georgiboy go weak at the knees with excitement!

Made my day

[georgiboy]

I do like an array formula, they do away with the use of VBA...

I have been busy all weekend but feel like you guys are waiting for me to have a play with this. I have had a go with no macro's at all nor any power query. I think I have answered both questions in the attached file.

I have:
Converted your range A:E into a table object.
Added an array formula to cell AC2
Added an array formula to cell AK2
Added an array formula to cell AN1
Added an array formula to cell I37

Short explanation of the formulae:

AC2:

=LET(
t,LET(d,--INDEX(TEXTSPLIT(G34,," "),2),t,Table2,FILTER(t,(INDEX(t,,1)<=TODAY())*(INDEX(t,,1)>=TODAY()-(d-1)))),
ops,IF(INDEX(t,,2)>=G37,G37,NA()),
scan1,SCAN(0,ops,LAMBDA(x,a,IF(NOT(ISNA(a)),x,x+1))),
scan2,SCAN(0,ops,LAMBDA(x,a,IF(NOT(ISNA(a)),x+1,0))),
plot,IF(MAXIFS(scan2,scan1,scan1)>2,G37,NA()),
HSTACK(t,ops,scan1,scan2))

This formula mimics your data in A:E and adds a few columns that allow me to filter out the dates for the opportunities data in cell I37. This formula expands with the table that was created in A:E.

AK2:

=IF(INDEX(AC2#,,8)>0,IF(MAXIFS(INDEX(AC2#,,8),INDEX(AC2#,,7),INDEX(AC2#,,7))>2,G37,NA()),NA())

This formula finds the opportunities and places the value in cell G37 if it finds one. This formula will auto expand as it feeds off of the array formula in cell AC2.

AN1:

=VSTACK(AC1:AK1,HSTACK(AC2#,AK2#))

This formula turns the headers AC1:AK1 as well as both AC2 & AK2 array formulae into one single array formula. I have done this as the chart you have will then expand itself to fit this array formula. So the formula in AN1 feeds the chart.

I37:

=LET(
t,SORT(CHOOSECOLS(FILTER(AC2#,NOT(ISNA(INDEX(AC2#,,6)))),1,7,8),{2,1},{1,-1}),
i,UNIQUE(INDEX(t,,2)),
tt,HSTACK(XLOOKUP(i,INDEX(t,,2),INDEX(t,,1)),XLOOKUP(i,INDEX(t,,2),INDEX(t,,3))),
d,CHOOSECOLS(FILTER(tt,INDEX(tt,,2)>2),2,1),
TEXT(INDEX(d,,2)-(INDEX(d,,1)-1),"dd/mm/yyyy") & " > " & TEXT(INDEX(d,,2),"dd/mm/yyyy"))

This formula takes the max count of each opportunity along with the max date of each opportunity. It then creates two dates by taking away the max count from the max date. This then gives us a start date and end date for each opportunity. It takes those two dates and joins them together as a text value separated by a > symbol.

There may be other ways to do this or I think an opportunity to simplify the formula, I may look at this soon but for now I have run out of time.

For now, the whole thing is based on the value being greater than the value you place in cell G37, there is no buffer it has to be greater than. Also, it looks for any opportunities that are greater or equal to 3 days rather than just it being 3 in a row.

Let me know what you think?

[Paul_Hossler]

Now if you can combine the formulas in a single one .....


But this old guy still prefers a VBA solution

[georgiboy]

Now that is a challenge... I may well have a go as I don't like the fact that the formula in AN1 just merges two other formulae together.

[georgiboy]

Here is a version that uses less array formula and more understandable formula in the table itself. I have also made the low and high values update themselves when you add data to columns A:C.

[p45cal]

Your right Paul, as when I changed the temp value to 25, it had a wobbly and threw Calc errors as well.

That value doesn't matter showing an error, in fact it's useful, since it occurs when there are no opportunity dates and so leads to the errors in column F which errors are not charted. Perfect.

@P45cal. The following formulas could be secret rocket launch codes for all I know. Just exactly what do they mean, and remember you are speaking with a convict here so go gently with the wording.

=VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,BYROW(R36#,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE))

and

=VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,BYROW(R36#,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),Table1__2[@Date],"¬",TRUE))

Having opted to use BYROW, it's a pain to work with since it won't output data spilling into different numbers of cells for each row (it won't produce a jagged array), so I elected to make its output one cell per row by using TEXTJOIN, then used it again to put all the results into a single (virtual) cell, then later I used TEXTSPLIT to break that cell out into the individual dates in a single column. Untidy. Convoluted. Not good.

and this one is

=LAMBDA(myDates,maxes,celsius,LET(g,SORT(FILTER(myDates,ABS(maxes-celsius)<=1)),f,SORT(g)-SEQUENCE(COUNT(g)), _
e,UNIQUE(f),c,DROP(FREQUENCY(f,e),-1),d,XLOOKUP(e,f,g),b,FILTER(HSTACK(c,d),c>=3),result,TEXTSPLIT(TEXTJOIN("¬",TRUE, _
BYROW(b,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE),b))(A2:A78,B2:B78,Temperature)

Well, I'm not going to go into lengthy explanations of each of the newish functions I've used; it took long enough to put them together in the first place!
In the attached, I've done some step by step columns in columns R:AQ, starting with the original lambda in cell P3. Hopefully this will break it down enough for you?

and before you get started what the hell is this? "¬". What was it meant to be before it got smashed by a truck?

It's a delimiter I used to split and join text. I use a rare character (one that's unlikely to occur in the source data to avoid splitting things in the wrong place). It's a habit, and in this case didn't need it; it could've been just a space.

[p45cal]

later on when the temps fall the Minimums will be a deciding factor.

While I'm at it, I tweaked the formula, so the attached:

• uses temperature thresholds (no data validation dropdown to allow free entry of any values) in cells I37 and I38
• plots those thresholds as dotted lines on the chart
• plots green blocks on the chart to show opportunities.

Hopefully this makes it a bit more visual and easy to check it's giving the right results

(Still no 'last n days' adressed!)

[Bob Phillips]

The following formulas could be secret rocket launch codes for all I know. Just exactly what do they mean, and remember you are speaking with a convict here so go gently with the wording.

=VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,BYROW(R36#,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE))

and

=VALUE(TEXTSPLIT(TEXTJOIN("¬",TRUE,BYROW(R36#,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),Table1__2[@Date],"¬",TRUE))

and this one is

=LAMBDA(myDates,maxes,celsius,LET(g,SORT(FILTER(myDates,ABS(maxes-celsius)<=1)),f,SORT(g)-SEQUENCE(COUNT(g)), _
e,UNIQUE(f),c,DROP(FREQUENCY(f,e),-1),d,XLOOKUP(e,f,g),b,FILTER(HSTACK(c,d),c>=3),result,TEXTSPLIT(TEXTJOIN("¬",TRUE, _
BYROW(b,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE),b))(A2:A78,B2:B78,Temperature)

I'll have a go at partially answering Ted.

First, note that LET is a way of doing a step by step calculation, similar to using helper cells, all in one formula. So you declare a variable, and then assign a value or a function to that variable, you can use that variable in a later step,

LAMBDA is a a custom function, somewhat akin to a UDF, and you declare the parameters that are passed to the LAMBDA function up front, then the code to process those parameters.

If you stagger the code you can better see each step and you should be able to work out what each step does.

=LAMBDA(myDates,maxes,celsius, 
        LET(g,SORT(FILTER(myDates,ABS(maxes-celsius)<=1)),
            f,SORT(g)-SEQUENCE(COUNT(g)),
            e,UNIQUE(f),
            c,DROP(FREQUENCY(f,e),-1),
            d,XLOOKUP(e,f,g),
            b,FILTER(HSTACK(c,d),c>=3),
result,TEXTSPLIT(TEXTJOIN("¬",TRUE, BYROW(b,LAMBDA(i,TEXTJOIN("¬",TRUE,SEQUENCE(,TAKE(i,,1),TAKE(i,,-1)))))),,"¬",TRUE),b)
       )(A2:A78,B2:B78,Temperature)

... what the hell is this? "¬". What was it meant to be before it got smashed by a truck?

That is just a character being used as a delimiter, it is one that is unlikely to occur in any of your data (I always use CHAR(1) to achieve the same).

[Aussiebear]

...... the Gods have spoken unto me, particularly the last post.

I'll keep that run over character for later, who knows its probably a lost key to this place.