Get previous score of user x-CodePudding

Consider the following data set:

     A:User  |  B:Date     | C:Score  | D:DiffLastResult
1:   John      2021-01-01     7
2:   Jane      2021-01-01     7
3:   James     2021-01-01     8

4:   John      2022-01-01     4
5:   Jane      2022-01-01     9
6:   James     2022-01-01    10

7:   John      2022-06-01    10
8:   Jane      2022-06-01     5
9:   James     2022-06-01     7

Now, I want in column D to have the abs difference between the current score and the previous score (for the given user). So, for instance, user James' last score is 7. Previous score of James was 10, so the delta is minus 3, which should be displayed in cell D9. In cell D6, I want to have a value of 2 (10-8, previous score of James, in context of the score of 2022-01-01).

This list is simplified, for the purpose of asking this question. In my real file, the list of names is unorderded, non-repetitive (not all users have the same amount of scores)

I am using Google Sheets. I have tried using vlookup, lookup, and index/match combinations, but I keep getting the first score of James (instead of the previous one). The list is sorted on date ASC.

Can somebody point me in the right direction? Many thanks.

CodePudding user response：

try:

=ARRAYFORMULA(IFNA(VLOOKUP(
 A1:A&COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A))-1, {
 A1:A&COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A)), C1:C}, 2, )))