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

New contributor
Steve Brown is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

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 at 22:22

Your Answer

Steve Brown is a new contributor. Be nice, and check out our Code of Conduct.

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.