2

Suppose I have this sheet showing which group (1, 2 or 3) each student joined on each day of class. Column B indicates in which group he/she last showed up, which is obtained by using the following formula :

=INDEX(FILTER($C3:3;$C3:3<>"");COUNTA($C3:3))

However, every time I add a new student, I must remember to copy the formula in the row I have just created. Can this be avoided? It seems that in other cases, an array formula can do the trick. Here, however, the existing formula already uses ranges, so that I do not see how it should be transformed. Of course, other suggestions besides using an array formula are welcome…

Improve this question
1
  • Hello! Just added a new Edit to make it a little bit shorter and more direct. It's in a new tab in the same sheet. Let me know ;)
    – Martín
    Oct 29, 2022 at 16:36

2 Answers 2

Reset to default
0

It's true that arrayformula won't almost surely work. But you can do it with BYROW and a LAMBDA formula,

=byrow(C3:C;lambda(eachrow;if(offset(eachrow;;-2)="";"";INDEX(FILTER(indirect("C"&row(eachrow)&":"&row(eachrow));indirect("C"&row(eachrow)&":"&row(eachrow))<>"");COUNTA(indirect("C"&row(eachrow)&":"&row(eachrow)))))))

The first part of the lambda function [if(offset(eachrow;;-2)="";""] looks for A column and leaves the cell empty if it is empty. And the INDIRECT functions are meant to establish that "C3:3" option, row(eachrow) will return the number of row (3, 4 and so on). Let me know if it works for you, if it's what you need or not, and if you'd need another explanation of this process. Here you have a sheet with it working! https://docs.google.com/spreadsheets/d/13yBJPF3qTvyAZsmHSJBIq5oWr2RPwh4ZvDdHP1S7EBk/edit?usp=sharing

EDIT

I've just realized it could be a little simpler and shorter if you just take range A3:A. That way you don't have to use OFFSET

=byrow(A3:A;lambda(eachrow;if(eachrow="";"";INDEX(FILTER(indirect("C"&row(eachrow)&":"&row(eachrow));indirect("C"&row(eachrow)&":"&row(eachrow))<>"");COUNTA(indirect("C"&row(eachrow)&":"&row(eachrow)))))))

Just a little shorter 🤣

Improve this answer
7
  • I had no idea this BYROW function even existed. It works fine. Thanks. My only doubt concerns the "real" table which is much bigger (600 rows x 700 columns) : do the string operations entail longer calculation time than with the replicated formula working on a single-row basis… This needs to be checked.
    – jmichel
    Oct 29, 2022 at 17:18
  • Me neither! I learned about it yesterday! You'd have to try, I don't think that should take that longer actually than with single formulas in each row... And with that IF condition you avoid having a lot of unnecesary calculations in the empty rows you may have. Let me know when you try it!
    – Martín
    Oct 29, 2022 at 17:25
  • I have implemented this formula in the big sheet and it does not seem to produce excessive slow down. The only change I made was to use a (limited) named range "Student" instead of A3:A. This allows me to avoid running the calculation over cells containing other stuff below the rows of interest. Inserting extra rows at the end of the student list automatically extends the named range to which the formula applies.
    – jmichel
    Oct 29, 2022 at 19:04
  • One further feature, which I did not expect is that, if one sorts the entire range by student name (which I happen to be doing quite often), the formula remains anchored to the first cell of the column and the calculation carries on properly. Great !
    – jmichel
    Oct 29, 2022 at 19:04
  • Excellent!! One further thing you can do is adapt the same formula and put it in the header (C2) So it says that if it is row 2 it will say "Last seen in group #" and from row 3 it will apply the formula. If you share the spreadsheet, you can be safe that no one (nor any sorting) will touch it ;) (You can see it in the copy of the sheet) =byrow(A2:A;lambda(eachrow;if(row(eachrow)=2;"Last seen in group #";IF(eachrow="";"";INDEX(FILTER(INDIRECT("C"&ROW(eachrow)&":"&ROW(eachrow));INDIRECT("C"&ROW(eachrow)&":"&ROW(eachrow))<>"");COUNTA(INDIRECT("C"&ROW(eachrow)&":"&ROW(eachrow))))))))
    – Martín
    Oct 29, 2022 at 21:31
0

If 'group' is only ever a single digit, here is an even shorter construction you can use in B5 provided there is nothing in column A after the last name:

=arrayformula(right(transpose(trim(query(transpose(C5:index(I5:I,counta(A5:A))),,9^9))),1))

In essence we are using QUERY to concatenate the contents of the 2D array of groups into a 1D array, then using RIGHT to return the last character in each element, which corresponds to the last group for each person.

EDIT: I just remembered that you could use BYROW to employ the same principle:

=byrow(C5:index(I5:I,counta(A5:A)),lambda(row,right(concatenate(row),1)))
Improve this answer
1
  • I would like to keep the option to use more than one character in the ID of the groups. Nonetheless it is interesting for me to see that the array formula approach was also feasible in this case.
    – jmichel
    Oct 29, 2022 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.