Using helper columns to filter data

[chrisou]

HI Guys

In the end what I am trying to do is to identify the start and finish times of a series as shown in the tables in the atatched file

I thought to solve the problem by creating a helper column in column K in the attached file which would check in the above cells in column J whether the format has been produced or not during the day (ie Gn 1/3, Bol or verrines). If not, then a 1 would be allocated in column K and this until the format changes over. If the new format for that day has not yet been previously produced during the day, then again a 1 would be allocated.

For June 3 data, the format Bol is produced once but then again at 17:46. Ideally the formula would take this into account and place a 2 in the column K, etc as hightlighted in the attached file.

I would most appreciate any help or alternative ideas for the formula in column K to help identify whether it is the first series of a particular format for that day or the second

[CARACALLA]

N18=IF(N$16=$N$16,IFERROR(AGGREGATE(15,6,$B$2:$B$451/($A$2:$A$451=$M18)/($J$2:$J$451=N$15)/($I$2:$I$451>0)/($K$2:$K$451=(LEFT(N$17,1)+0)),1),""),IFERROR(AGGREGATE(14,6,$B$2:$B$451/($A$2:$A$451=$M18)/($J$2:$J$451=N$15)/($I$2:$I$451>0)/($K$2:$K$451=(LEFT(N$17,1)+0)),1),""))

Copy across and down

[HansDouwe]

Please try for start time 1st serie:

Please Login or Register  to view this content.

Please try for finish time 1st serie:

Please Login or Register  to view this content.

Please try for start time 2nd serie:

Please Login or Register  to view this content.

Please try for finish time 2nd serie:

Please Login or Register  to view this content.

Format cells: uu:mm:ss;;
Copy across per block.

[chrisou]

HI Caracalla
Your formulas are working perfectly thank you. However, to calculate the start and finish times in your formulas you are using the data in column K that I inserted manually.
I need a formula for column K as a result to calculate when the format changes. Always a 1 for each new format during that day and a 2 if it is the second series during the day of that format (this happened on 3rd June for the BOL and Verrines)
Many thanks for your help

[chrisou]

HI HansDouwe
Unfortunately I have only Excel 2013 and cannot use your formulas.
Additionally I need a formula for column K (whose data has been entered manually) to calculate when the format changes. Always a 1 for each new format during that day and a 2 if it is the second series during the day of that format (this happened on 3rd June for the BOL and Verrines)
Many thanks for your help