Arrayformula to find next value for criteria in Google Sheets

Question

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    |                     |
+---------------------+--------------+--------+---------------------+

agree with Player0. This is a fairly "known" solution, but it's just custom enough that seeing the sheet is important — MattKing, Feb 06 '20 at 21:55
[link]https://docs.google.com/spreadsheets/d/1aI43GS5ynDxlFRdDtXeKU1knKWFkWFwqk6MBoaDfysc/edit?usp=sharing — Piotr Szpitalny, Feb 06 '20 at 22:30

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

try:

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

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)))}

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 06 '20 at 21:55

player0

124,011
12
67
124

I have a new trick for these, that i'm looking forward to showing you if he posts a sheet – MattKing Feb 06 '20 at 21:55
That picks up the latest datestamp for the name so its not what i am looking for – Piotr Szpitalny Feb 06 '20 at 22:31
[link]https://docs.google.com/spreadsheets/d/1aI43GS5ynDxlFRdDtXeKU1knKWFkWFwqk6MBoaDfysc/edit#gid=0 – Piotr Szpitalny Feb 06 '20 at 22:32
@PiotrSzpitalny It's much easier to demo on the shared sheet itself, it'd be great if you could make it editable. – MattKing Feb 06 '20 at 22:57
1

well done. I'm having trouble using the trick i thought i knew. It might work only on a vookup for backwards looking timestamps. In your answer, is there any reason for the FILTER on the range? i found this to work: =ARRAYFORMULA({"Arrayformula";IFERROR(VLOOKUP(C2:C&COUNTIFS(C2:C,C2:C,A2:A,"<"&A2:A)+1,{C2:C&COUNTIFS(C2:C,C2:C,A2:A,"<"&A2:A),A2:A},2,0))}) – MattKing Feb 07 '20 at 13:49
@MattKing idea with filter is to get rid of all 1's from `=ARRAYFORMULA(ROW(C2:C)-MATCH(C2:C, C2:C, 0))` – player0 Feb 07 '20 at 14:37
@player0 figured out how to do forward facing lookups with my substitute() method – MattKing Feb 08 '20 at 17:36
1

Thanks for help with this! – Piotr Szpitalny Feb 10 '20 at 20:39
Nice! Please note: key-column should not end with a number of a row. key "Ben3" may affect the formula – Max Makhrov Dec 14 '21 at 13:51

score 1 · Answer 2 · answered Feb 08 '20 at 17:34

1

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)))})

answered Feb 08 '20 at 17:34

MattKing

7,373
8
13

Nice! please note, works for finding the next numbers, not text. – Max Makhrov Dec 14 '21 at 13:37

Arrayformula to find next value for criteria in Google Sheets

2 Answers2

UPDATE: