My student information system gives me multiple rows per student, each row showing one period in their schedule. I want rearrange the data so each row is the complete schedule for one student. Put another way, my source gives me:

enter image description here

What I want is this: enter image description here


1 Answer 1


Use query() with a pivot clause, like this:

=query(A1:C21, "select A, max(C) where A is not null group by A pivot B", 1)

If you need to handle multiple blocks listed in one cell in column B, you will have to split the column and insert additional rows.

  • Good job,doubleunary. Before using your formula he will have to modify the row (or rows) that contain a double Block, such as cell B21, and add a new row, so that he can then extend the calculation to the whole WhatIGot!A1:C
    – Daniele
    Jun 23, 2022 at 22:22
  • Yes! Excellent. Jul 11, 2022 at 12:21
  • I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? Jul 11, 2022 at 15:48

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.