Helper columns to solve problem

[chrisou]

HI Guys
In the attached spreadsheet I am trying to identify each time the format changes during the day in column J such as to eventually identify the start and finish time of the format (as explained in column K). I have tried in column J to create a formula to detect the format changeover in column C but the zero values in column C are leading to errors in my formula.
Do I need to create extra helper columns to help sort the data? I would appreciate any help possible
Thanks
Chris

[arnelgp]

you can try to use this formula in Cell, J2:

=IF(ROW()=2,1,IF(C2<>C1,1,0))


copy it down, make sure to set your Calculation to Automatic.

[Aussiebear]

Sadly your function will not work as an If(Or since it cannot hold more than two tests by its very nature. If(Or(Logical test1, Logical Test2, Result if true, Result if false).

[Aussiebear]

Perhaps try a derivative of arnelgp's formula.... =If(And(Row()=2,C2<>C1,B2,""))

[chrisou]

Hi GuysThanks for your help and suggestions. I have not managed to put the proposed formula but I have tried something else which iseems to be OK in column J.

The problem I have now is that I want to use column J to detect when the format has changed such as to enter the start and finish time of a series of a particular format for each day. IN the table the days of 1st and 2nd June give the correct start and finish time but this is based on the first and last products of that format during the day. However, if there are more than one series of that format during the day (ie seperated by another format) the formula does not work.

The ideal solution shows what I am trying to achieve with the start and finish times for each series of a format (especially if there is more than one run of that format during the day).Many thanks for you help

[snb]

In K2:
=IF((COUNTIF($K$1:$K1;C2)=0)*(C2<>0);C2;"")

[chrisou]

HI Guys

Thanks for all your help and proposals.
In the end what I am trying to do is to identify the start and finish times of a series.

I though that 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

[snb]

In the end what I am trying to do is to identify the start and finish times of a series.
C'est exactement ce que ma Code fait.

[chrisou]

Hi SNB
Thanks for your reply. Your code highlights the first time the format appears (Gn 1/3, Bol, Verrines) but afterwards when the formula is copied down it does not identify the next time those formats are produced on the following days - it only identifies it once. As per my previous message, I would like to identify each time a format is changed for that day by a 1 and if the format is produced again later in the day by a 2, etc as per the numbers I have inputed manually in column J.
Ideally I would like to have an automatic formula for this instead in column J
Thanks for your help and sorry for the confusion

[p45cal]

In the attached a pivot table at cell K12 derived from a Power Query transformation using only columns A:I.
Update the data in the table on the left then refresh the pivot to see new results.
30162

[chrisou]

Hi p45cal
Thank you very much for investigating this and your proposed solution. However, I have Excel 2013 and it does not seem to work with Power Query?
Kind regards
Chris

[Aussiebear]

I believe that Power Query came with the introduction of Office 365

[arnelgp]

Power Query is a downloadable Add-in for Excel 2013
Download Microsoft Power Query for Excel from Official Microsoft Download Center (https://www.microsoft.com/en-us/download/details.aspx?id=39379)

[p45cal]

I believe that Power Query came with the introduction of Office 365Excel 2016 built-in (still after Excel 2013).

[Aussiebear]

Well there you go then. I need to ask the jail authorities to update my reading material by 20 years.