Search code examples
google-sheetsgoogle-sheets-formulaarray-formulascountinggs-vlookup

Finding closest greater from a filtered range

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.

Solution

  • 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