2

I have a sheet with information regarding the operation time of two machines. It has three main columns: "A" is the number of the machine; "B" is the starting time and date of operation; and "C" is the ending time and date of operation.

I'm trying to write a formula (fourth column) that finds the "starting time" of the next operation - conditioned to the machine number. For this, I have to input the "ending time" and find the closest greater "starting time" from another line - conditioned to the machine number.

I've tried two ways to doing that, none have worked 100% correctly.

First, I'm "filtering" the range of B, conditioned to A. Then, I'm inserting this filtered range into a formula that looks for the closest greater value.

An example sheet would be the following (the colors are just for tracking/debugging purposes, they don't have to be on the formula):

enter image description here

These are the two formulae I've tried:

=small(filter(B:B;A:A=A1);COUNTIF(filter(B:B;A:A=A1);"<"&C1)+1)

=INDEX(filter(B:B;A:A=A1);MATCH(MIN(ABS(filter(B:B;A:A=A1)-C1));ABS(filter(B:B;A:A=A1)-C1);0))

In the first example, only the last results came back right. The rest of the column repeated the same value.

The second is erratic, sometimes bringing the closest greater, sometimes the closest smaller number, but works better than the first one.

player0
  • 124,011
  • 12
  • 67
  • 124
zfg
  • 195
  • 1
  • 1
  • 8

1 Answers1

1

try:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&
 COUNTIFS(A2:A; A2:A; ROW(A2:A); "<="&ROW(A2:A))+1; FILTER({A2:A&
 COUNTIFS(A2:A; A2:A; ROW(A2:A); "<="&ROW(A2:A))\ 
 TEXT(B2:B; "dd/mm/yyyy hh:mm:ss")}; 
 COUNTIFS(A2:A; A2:A; ROW(A2:A); "<="&ROW(A2:A))>1); 2; 0)))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I tried this but it didn't work. For some reason I get only blank cells. – zfg Oct 24 '19 at 12:08
  • It worked almost perfectly, thank you so much! There is only one issue though: if the lines are repeated, the first line gets the starting time of the second line, instead of the following event. For this I thought of maybe making a conditional and an extra column, to filter them out.. what do you think? – zfg Oct 24 '19 at 18:47
  • not rly sure what do you mean... https://i.stack.imgur.com/o38cH.png – player0 Oct 24 '19 at 18:55