A hopefully very easy question

Hi everyone

I have to prepare an exam sheet for the teachers of a particular course (600+ students), and I have no experience with LibreOffice Calc whatsoever. So I hope that this is a very easy question.

At the moment my document contains the students names in column A, and grades from different assignments in column B. They have handed in 7 assignment, which means that in column A, the first students name is in cell A1 to A7 with the grades from each assignment in B1 to B7, the second students name is in A8 to A14 with grades in B8 to B14, etc.

What I want is, that the first students name only appears in cell A1 and the grades would be in B1 to H1, the second students name should be in cell A2 and the grades in B2 to H2, etc. Is there any clever way to do this? Due to the large amount of students, I would very much prefer not to have to repeat the same command 600+ times.

I would very much appreciate any help you people out there can give me!

Best regards,

  • Jamie Gabe

A pivot table should work. Add a new column to your data between the name and grade columns. Title the three columns in row 1 as Name, Test and Grade.

In cell B2 add the formula =MOD(ROW()-2,7) and copy it down the column. This will label the tests with numbers 0 through 6.

Select all the data and choose Data->Pivot Table->Create. In the dialog box. Click on the Name button and drag it to the Row Fields section; the Test button to the Column Fields section and the Grade button to the Data Fields section. Click OK to create a pivot table on a new sheet.

To finally meet your requirement, select only the name and grade data from the pivot table, Copy it and Paste into a new sheet to cell A1

Thank you very much!

The pivot table is maybe a somewhat forgotten option, but very versatile, the @w_whalley answer is a demonstration.

Also this can be made with formulas, attached a file with a formula solution.