CODEWITHSUNDEEP
google-sheetscountgoogle-sheets-formulacountingcountif
Exel
Exel

Reputation: 45

Formula to get next instance of row that contains specific text that works in ARRAYFORMULA

I'm building out a time tracking form and sheet.

I have everything working and I am able to get the next instance of a row that contains that user's name to get the elapsed time they were in that status.

The formula I am currently using is this:

=ArrayFormula(iferror(INDEX($A2:$A,SMALL(IF(B2=$B3:$B,ROW($B$2:$B)),1)), NOW()))

However, this does not work in an ARRAYFORMULA.

I've tried:

=ARRAYFORMULA(VLOOKUP(B2:B, {INDIRECT("B"&ROW(A2:A)+1&":B"), INDIRECT("A"&ROW(A2:A)+1&":A")}, 2, FALSE))

Which doesn't work in array formula because of INDIRECT.

=ARRAYFORMULA(SORTN(FILTER(A3:A, B3:B=B2), 1))

Doesn't work in ARRAYFORMULA

=ARRAYFORMULA(QUERY(A3:C, "SELECT MIN(A) WHERE B = '"&$B2&"' label MIN(A) ''"))

Doesn't work in ARRAYFORMULA.

These formulas all work if I drag them down manually but I don't want to have to open this sheet every couple of hours to drag it down.

Please help!

Link to sheet where I've been messing with formulas:

https://docs.google.com/spreadsheets/d/1ZZOFTFlhmanQPNfRreT2bBinlkb00uaK-qypmEMD3ww/edit

Upvotes: 3

Views: 583

Answers (1)

player0
player0

Reputation: 1

count it:

=ARRAYFORMULA(IF(B2:B="",,IFNA(VLOOKUP(
  B2:B&COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B)), 
 {B2:B&COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))-1, A2:A}, 2, 0), NOW())))

enter image description here

Upvotes: 3

Related Questions