Arrayformula to find next value for criteria in Google Sheets

1
votes

I have Form Responses tab in my sheets where I collate information containing starting datetimestamp, number and name. I want to create an arrayformula that will fill last column with datetimestamp for the next name entry in the dataset. I want to avoid the need of submitting finish datetimestamp.

I have tried 

=ARRAYFORMULA(if(A:A="","",vlookup(C:C,{C2:C20,A2:A20},2,false)))

hoping that arrayformula will change the search range as it fills the further rows but it didn't.

It Has to be arrayformula which will be in the first Row, cant use single formula and copy and paste it because its form response which adds row for every answer

        Now


+---------------------+--------------+--------+
|      Timestamp      | Group number | Picker |
+---------------------+--------------+--------+
| 29/01/2020 21:31:45 |         3001 | Ben    |
| 30/01/2020 19:42:20 |         3002 | Ben    |
| 30/01/2020 20:04:51 |         3006 | Jon    |
| 30/01/2020 20:05:08 |         3018 | Jon    |
| 30/01/2020 22:31:45 |         3019 | Ben    |
| 31/01/2020 23:31:45 |         3020 | Jon    |
| 01/02/2020 21:31:45 |         3021 | Ben    |
+---------------------+--------------+--------+





    Expected



+---------------------+--------------+--------+---------------------+
|      Timestamp      | Group number | Picker |    ARRAYFORMULA     |
+---------------------+--------------+--------+---------------------+
| 29/01/2020 21:31:45 |         3001 | Ben    | 30/01/2020 19:42:20 |
| 30/01/2020 19:42:20 |         3002 | Ben    | 30/01/2020 22:31:45 |
| 30/01/2020 20:04:51 |         3006 | Jon    | 30/01/2020 20:05:08 |
| 30/01/2020 20:05:08 |         3018 | Jon    | 31/01/2020 23:31:45 |
| 30/01/2020 22:31:45 |         3019 | Ben    | 01/02/2020 21:31:45 |
| 31/01/2020 23:31:45 |         3020 | Jon    |                     |
| 01/02/2020 21:31:45 |         3021 | Ben    |                     |
+---------------------+--------------+--------+---------------------+
2
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
share a copy of your sheet with example of desired output – player0
agree with Player0. This is a fairly "known" solution, but it's just custom enough that seeing the sheet is important – MattKing
[link]docs.google.com/spreadsheets/d/… – Piotr Szpitalny

2 Answers

1
votes

try:

={""; ARRAYFORMULA(IFNA(VLOOKUP(C2:C, SORT({C2:C, A2:A}, 2, 0), 2, 0)))}

enter image description here

UPDATE:

={"ARRAYFORMULA"; ARRAYFORMULA(IFNA(VLOOKUP(
         C2:C&COUNTIFS(C2:C, C2:C, ROW(C2:C), "<="&ROW(C2:C))+1, 
 FILTER({C2:C&COUNTIFS(C2:C, C2:C, ROW(C2:C), "<="&ROW(C2:C)), A2:A}, 
 ROW(C2:C)-MATCH(C2:C, C2:C, 0)<>1), 2, 0)))}

0

0
votes

Finally figured out how to do this with the new trick I thought of for these types of lookups.

I realize @player0 has already answered this perfectly well!, But this should also work for this, and other similar types of lookups if anyone stumbles on this in the future:

=ARRAYFORMULA({"Arrayformula";IFERROR(-1/(1/(SUBSTITUTE(LOOKUP(C2:C&9^9-A2:A-0.0001,SORT(C2:C&9^9-A2:A)),C2:C,"")-9^9)))})