1

I have a table (Master sheet) in Google Sheets that looks something like this:

enter image description here

https://ethercalc.org/t8f0ze0kyx6a

I need to filter out an entire row of information on a separate sheet (Last Lesson) but only pertaining to someone's last lesson, eg. Sam and his third lesson. I've tried to add various different things to my filter option but with no success. My knowledge of Excel/Google Sheets is basic so if there is another way to go about this without the filter option do share.

  • Welcome. Please do share a link to a test sheet. Also, as per site guidelines when an answer addresses your question, you should accept and maybe upvote it so others can benefit as well . – marikamitsos Sep 17 at 14:41
  • =FILTER(A2:M9, A2:A9="Sam") This is what I used to get a list of lessons for this person. I tried incorporating a max function into this but it would either just give me a highest lesson value and not for the required name, or it would return an error. – Linda Sep 17 at 14:44
  • A link to a test sheet would be very helpful and a real time saver. You can edit your question and add it there. – marikamitsos Sep 17 at 14:48
  • I am a bit confused about what your end result should be. Please have a look at this sheet and tell me which solution you seek for, so I can turn it to an answer. – marikamitsos Sep 17 at 15:29
  • 1
    Please edit/update your question with the above comment. It is vital to help everyone understand your question. – marikamitsos Sep 17 at 17:25
1

You should use a formula combining several functions so as to achieve what you need. The working formula is the following:

=ArrayFormula(IFERROR
     (VLOOKUP(UNIQUE(Master!A1:A20),
        QUERY({Master!A1:M15},"Select * Order By Col13 Desc"),
            {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13},FALSE)))

EDIT
In your comment you mention:

Last lesson: The plan is to have everything from row 10 and below and column E and onward grouped. When a name is chosen in row 3 it should pull the info of that person's last lesson (see Sam). Below this little form is a filtered info from the main list of all the lessons. I figured I could keep adding names in column N12 onward and create a filter for each name to return their last lesson from New lesson sheet. The list of names and lessons will expand! – Linda

Here is the link to a working sheet.

PS: From your original Master sheet I deleted the redundant rows.

  • Marika, thank you for your efforts. I can't quite get it to work on my end probably due to many other formulas I have going on, but hopefully I'll figure it out! – Linda Sep 17 at 17:11
  • 1
    "I can't quite get it to work on my end...". That is why I was insisting on "Please do share a link to a test sheet". In any case, please do share a link to a test Google Sheet. We can help out. – marikamitsos Sep 17 at 17:15
  • I do not have a test sheet - only the main sheet which is used between me and my colleagues. As there are many sheets containing private information of our students I can not share the said file. – Linda Sep 17 at 17:57
  • Create a new spreadsheet, make a copy of the sheets in question, alter/sanitize students info, share with the world. (you already did the same when tried to share through ethercalc). – marikamitsos Sep 17 at 18:07
  • I've made your solution work! Can you just explain to me, when you find time, what each segment of this function does in case I ever need this type of thing again? – Linda Sep 17 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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