I have a long table (50k lines and 15 columns) where different users report a status using a google-form (the table is always chronologically sorted), and I want to obtain the last report of each user. I have managed to do it, but it takes too long to reload, I know that the formulas I use are too heavy for the spreadsheet, but I can't find any better way to do it.
To simplify the scenery, the case can be applied to the following input and desired output:
Input:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:00:30 | A | Started |
| 2019-01-03 10:01:41 | B | Started |
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:02:14 | C | Started |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
Output:
| Timestamp | User | Status |
|:-------------------:|:----:|:--------:|
| 2019-01-03 10:02:00 | B | Finished |
| 2019-01-03 10:03:22 | A | Failed |
| 2019-01-03 10:00:04 | C | Finished |
I managed to do it combining a pivot table with user as row and max(Timestamp) as value and then feed that information to a formula (maybe INDEX and MATCH would have been a better approach):
OFFSET('Sheet1'!$A$1,ArrayFormula(max(if('Sheet1'!$B:$B=$A3, Row('Sheet1'!$B:$B))))-1,column()-1)
I also tried with the following approach for each unique result.
| Unique users | Query |
|:----------------------:|:-----------------------------------------------------------------------------:|
| =UNIQUE('Sheet1'!$A:$A) | =QUERY('Sheet1'!$A:$C, "SELECT A, C WHERE B='"&B2&"' ORDER BY A DESC LIMIT 1",0) |
From what I have read, combining ARRAYFORMULA and QUERY might improve the performance, but I have not been able to pull it off.
Assuming Timestamp in col A, User in Col B and Status in col C try
=Arrayformula(iferror(vlookup(unique (B2:B), sort({B2:B, A2:C}, 2, 0), {2, 3, 4}, 0)))