Sorting and display groups of data entries

[ben.cote]

I'm working on a COVID project to count pedestrians. I have the data output but I need a way to summarize it into a report.

The formula has to find if the weather and road conditions have changed, and if they have changed, what period of time did they change over. If the count starts at 8:00 am with Sunny and Bare conditions and the weather and road conditions change at 9:00 am to Rain and Overcast, the Weather and Roads Report has to show that between 8:00 am and 9:00 am the conditions were Sunny and Bare. We do not require a full list of each entry over the time period from 8:00 am till 9:00 am just the summary.

The sample has been simplified but the principles are the same.

[FDibbins]

Try this.

1. add a counter to ID changes in main table...
N6=IF(AND(G6=G5,H6=H5),N5,N5+1)
copied down as needed. This will generate an increasing value for every change
This can be hidden if needed

2. Add a number sequence (1, 2, 3 etc) to your extract table. I used E23:E25 but you could put them on the left of the table instead. This can also be hidden.

3. for the extract.
(Because your headings are slightly different and you probably dont want to change them (start time/end time) I needed to make the formula bit more complex to cater for that)
A23=if($E23="","",INDEX($A$6:$M$14,MATCH($E23,$N$6:$N$14,0),MATCH(TRIM(LEFT(A$22,FIND(" ",A$22))),$A$5:$M$5,0)))
copied to C and D23
B23=if($E23="","",INDEX($A$6:$M$14,MATCH($E23,$N$6:$N$14,0)+COUNTIF($N$6:$N$14,$E23)-1,MATCH(TRIM(LEFT(B$22,FIND(" ",A$22))),$A$5:$M$5,0)))
Then copy them all down as needed

[ben.cote]

I want to thank you for the detailed information. I believe I've got the time start, time end working. I am plugging away at it, trying to grasp the logic of the formula, and at my age, that can take a while...lol

[ben.cote]

I must be missing something FDibbins. I couldn't get it to pick up the cell values and post them.

[bebo021999]

Is the main logic seeking timestamps of every change in Weather AND Road condition?
Try to compare column G and H, row by row like this:
($G$5:$G$14<>$G$6:$G$15)/($H$5:$H$14<>$H$6:$H$15)
Then mark row with row index:
ROW($G$5:$G$14)/($G$5:$G$14<>$G$6:$G$15)/($H$5:$H$14<>$H$6:$H$15)+1
equals {6;#DIV/0!;#DIV/0!;9;#DIV/0!;#DIV/0!;12;#DIV/0!;#DIV/0!;15} with the timestamp starts at row 6,9,12,15
To get the first smallest value (6)
AGGREGATE(15,6,ROW($G$5:$G$14)/($G$5:$G$14<>$G$6:$G$15)/($H$5:$H$14<>$H$6:$H$15)+1,ROW(1:1))
Then Index column A:
In A23:

Please Login or Register  to view this content.

Drag down as far as needed.
In B23:

Please Login or Register  to view this content.

In C23, then copy to D23:

Please Login or Register  to view this content.

Drag all down.

All assum that time in column A is increasing with no gap.

[FDibbins]

Change C23 to...
=IF($E23="","",INDEX($A$6:$M$14,MATCH($E23,$N$6:$N$14,0),MATCH(C$22,$A$5:$M$5,0)))
copied across

Then copy all down

[ben.cote]

I finally got a look at the original count worksheet and it starts at 12:00 AM. I don't know who would be walking at that time of night but I guess they do other people counts with it. I changed the start time and POOF. I looked at the formula but I couldn't figure out why by changing the time, it died. Sorry to be such a pain. You have been so much help and I really appreciate it. I have been looking up to commands you are using to try and learn more and troubleshoot the issues myself but I'm not that good yet. I learn lot slower at my age. Thank you again.

[bebo021999]

See attachment with new update version