• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Start & End Date if 6 or more Consecutive Days

Need a formula or VBA Code which will check the 3 criteria on Site ID, Observation and if there are 6 consecutive continuous dates.

Data sheet is to be used to get the output.

For e.g.
Criteria 1 - Checks if Site ID is A1000.
Criteria 2 - Checks if Observation is "AC is not working".
Criteria 3 - If there are 6 consecutive continuous dates in Date of Observations.

If all the three criteria matches then it should show the Start Date and End Date.

In the Output Sheet, Site ID and Observations are already mentioned. Need the formula or VBA Code for Start Date and End Date.

Output Cells where Formula or VBA Code is required are colored in yellow in the Output Sheet.

Thanks in Advance :)
 

Attachments

  • Penalty.xlsx
    14.3 KB · Views: 9
Amit Punia
Sample as You have written ...
Notes:
Your criteria 3 seems to have two different variations. I followed that, which You have mentioned twice.
I didn't get 15/09/2020-18/09/2020 -required result ...hmm? (I left 'ORG Output'-sheet for reference.)
 

Attachments

  • Penalty.xlsb
    29.7 KB · Views: 4
Dear @vletm ,

Just Consider that there is only one Observation and i.e. "AC is not working"

Now if you could work on all the three criteria.

Criteria 1 - Checks if Site ID is A1000.
Criteria 2 - Checks if Observation is "AC is not working".
Criteria 3 - If there are 6 consecutive continuous dates in Date of Observations.

I've attached the file again with just one observation.

Need any formula or VBA Code. The output needed in the yellow cells of output sheet.

It should give the start date and end date if Site ID is A100, Observation is AC is not working and there are 6 consecutive continuous dates in the date of observations like Sept 4, Sept 5, Sept 6, Sept 7, Sept 8 and Sept 9. If this is true then Start Date should be Sept 4 and End Date should be Sept 9.
 

Attachments

  • Penalty.xlsx
    14.2 KB · Views: 2
Amit Punia
Okay - there are none my formulas!
but try to do next steps - with both eyes open:
1) Select 'output'-sheet
2) Clear rows 3 to 10.
3) Select 'data'-sheet.
4) Go back to 'output'-sheet.
5) Is it still same as after Your clearing.
Later .. no need to do step #2!
 
Attached, not quite what you asked for but something you may be able to use.
There's a table at cell A10 of ther Output sheet, just right-click and choose Refresh to update it.
It lists consective dates of 2 days or more.

Not related to this offering at all is a formula in column F of the Data sheet which ascribes each date an attribute that should be self explanatory.
 

Attachments

  • Chandoo44943Penalty.xlsx
    27 KB · Views: 8
Dear @p45cal

The solution is very interesting. Needed something like this. Can work around with output.

Just wanted to understand how you populated the data in cell A10 in Output Sheet because when I'm trying to refresh the data, it's showing me the Initialization of the Data Source Dialogue Box(image is attached in this thread)

Initialization.JPG.
 
Dear @vletm ,

I tried the steps you asked me to do however I'm still not able to get the desired output.

Maybe I'm missing something in the steps you shared.
 
Amit Punia
Did You those four steps as I wrote?
It will give as below snapshot:
Screenshot 2020-09-05 at 21.33.28.png
... and ...
of course, You have read my notes from reply #2.
What is missing?
... and ... macros have to be enabled!
 
because when I'm trying to refresh the data, it's showing me the Initialization of the Data Source Dialogue Box(image is attached in this thread)
1. Does it work in the file I attached, before making any adjustments (delete a few cells in the result table at cell A10 then refresh and see whether values are re-instated)?
2. The source data needs to be in an Excel Table proper, of the same name as the source data table's name, ie. Table1. You should be able to paste data on top of the existing table and adjust the table's range if necessary using the little triangular drag-handle at the bottom right of the table. Keep the column names the same.
3. If you still have problems, attach the problem file here.

To answer your question, it's Power Query.
 
This is a LET formula for Microsoft 365 users only.
Code:
= LET(
  matches,    FILTER(SR_No, Observations=specificObservation),
  mismatches, FILTER(SR_No, Observations<>specificObservation),
  freq,       FREQUENCY(matches, mismatches),
  No,         IF(freq>5, mismatches),
  endNo,      FILTER(No, No),
  seq,        FILTER(freq, freq>=5),
  startDate,  INDEX(date, endNo - seq),
  endDate,    INDEX(date, endNo - 1),
  CHOOSE( {1,2,3}, seq, startDate, endDate) )
 

Attachments

  • Penalty.xlsx
    16.4 KB · Views: 5
Please set Date criteria at I3

C3
=IF(D3="","",D3-INDEX(FREQUENCY(IF((Data!$B$2:$B$62=$A3)*(Data!$C$2:$C$62=$B3),IF(Data!$D$2:$D$62+1=Data!$D$3:$D$63,Data!$D$2:$D$62)),IF((Data!$B$2:$B$62=$A3)*(Data!$C$2:$C$62=$B3),IF(Data!$D$2:$D$62+1<>Data!$D$3:$D$63,Data!$D$2:$D$62))),AGGREGATE(15,6,ROW($A$1:$A$9)/(FREQUENCY(IF((Data!$B$2:$B$62=$A3)*(Data!$C$2:$C$62=$B3),IF(Data!$D$2:$D$62+1=Data!$D$3:$D$63,Data!$D$2:$D$62)),IF((Data!$B$2:$B$62=$A3)*(Data!$C$2:$C$62=$B3),IF(Data!$D$2:$D$62+1<>Data!$D$3:$D$63,Data!$D$2:$D$62)))>$I3-2),INT((COLUMNS($C3:C3)+1)/2))))

D3
=IFERROR(INDEX(Data!$D:$D,SMALL(IF((Data!$B$2:$B$62=$A3)*(Data!$C$2:$C$62=$B3),IF(Data!$D$2:$D$62+1<>Data!$D$3:$D$63,ROW(Data!$D$2:$D$62))),AGGREGATE(15,6,ROW($A$1:$A$9)/(FREQUENCY(IF((Data!$B$2:$B$62=$A3)*(Data!$C$2:$C$62=$B3),IF(Data!$D$2:$D$62+1=Data!$D$3:$D$63,Data!$D$2:$D$62)),IF((Data!$B$2:$B$62=$A3)*(Data!$C$2:$C$62=$B3),IF(Data!$D$2:$D$62+1<>Data!$D$3:$D$63,Data!$D$2:$D$62)))>$I3-2),INT((COLUMNS($C3:D3)+1)/2)))),"")

copy C3:D3 to E3

or Power Query

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedT = Table.TransformColumnTypes(Source,{{"SR No", Int64.Type}, {"Site ID", type text}, {"Observations", type text}, {"Date of observations", type date}}),
    DateList = Table.FromValue(List.Dates( List.Min(ChangedT[Date of observations]),Number.From(List.Max(ChangedT[Date of observations])-List.Min(ChangedT[Date of observations])),#duration(1,0,0,0))),
    #"Sorted Rows" = Table.Sort(ChangedT,{{"Site ID", Order.Ascending}, {"Observations", Order.Ascending}, {"Date of observations", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Site ID", "Observations"}, {{"Count", each Table.Sort(Table.ExpandTableColumn(Table.NestedJoin(DateList,"Value",_,"Date of observations","Dd",JoinKind.LeftOuter),"Dd",{"Site ID","Observations"}),{{"Value", Order.Ascending}})}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value", "Site ID","Observations"}, {"Value", "Site ID.1","Observations1"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Count", {"Site ID", "Observations", "Value"}),
    #"Grouped Rows1" = Table.Group(#"Removed Duplicates", {"Site ID.1","Observations"}, {{"Min", each List.Min([Value]), type nullable date}, {"Max", each List.Max([Value]), type nullable date}, {"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows1", each ([Site ID.1] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Count] > 2)
in
    #"Filtered Rows1"
 

Attachments

  • Penalty.xlsx
    29.5 KB · Views: 15
Back
Top