Find position last mission

[Pierre]

Hello,

I work in warehouse where users move goods from one position to the other. I need to calculate the average position moved between the end of a mission and the start of the next one (=empty movement) for each user.
In the data I can extract, I know the date-time of the end of the mission, as well as position at beginning and at the end of the mission for each user. The data I extract are not sorted in any way. I'm looking for a formula that could, for each user/mission, find its position at the end of previous mission.
I'm thinking I should first find a way to attribute an order to each mission performed by a specific user? Or is there a possibility with a formula to find the information I need directly?
I enclosed a small example of data for just 1 day, but in reality I have several weeks of data

Thanks in advance for any insight

 

[John Jairo V]

Hi @Pierre !

You could try this formula in E2 and drag it down:
=IFERROR(INDEX(D$2:D$8,MATCH(AGGREGATE(14,6,B$2:B$8/(A$2:A$8=A2)/(B$2:B$8<B2),1),(A$2:A$8=A2)*(B$2:B$8<B2)*B$2:B$8,)),"")

Blessings!

 

[Pierre]

Hi @John Jairo V

Thanks a lot! Amazing formula!
The formula seems to do the trick when there is only a single day in the data, but when I add several days, it will also give me a position of the last mission perfomed for D-1 for the first mission peformed on day D. For the first mission performed by any user on any day, I should report "empty" data.
Would you have a way to satisfy this requirement?

 

[p45cal]

Power Query offering.
Right-click the green results table and choose Refresh should you update the data in the left hand table.

 

[John Jairo V]

Hi again!

Just add the new condition in both parts:
=IFERROR(INDEX(D$2:D$17,MATCH(AGGREGATE(14,6,B$2:B$17/(A$2:A$17=A2)/(B$2:B$17<B2)/(INT(B$2:B$17)=INT(B2)),1),(A$2:A$17=A2)*(B$2:B$17<B2)*(INT(B$2:B$17)=INT(B2))*B$2:B$17,)),"")

Blessings!

 

[Peter Bartholomew]

Somewhat more laboured using 365

= MAP(user, endTime,
    LAMBDA(u,e,
      LET(
        thisUser?, user=u,
        sameDay?,  INT(endTime)=INT(e),
        earlier?,  endTime < e,
        priorEnd,  IF(thisUser? * earlier? * sameDay?, endTime),
        XLOOKUP(e, priorEnd, endPosition,"",-1)
      )
    )
  )

 

[Pierre]

Hello all!
@p45cal and @Peter Bartholomew , thank you very much for different solution, I definitely wasn't aware it was possible to solve this problem with a power query, or this kind of code in formula. This is some high end approach, from what I can see it works perfectly and I have to check in more details how it is done

@John Jairo V, works like a charm, thank you so much for your input!