Ask Your Question

A hopefully very easy question [closed]

asked 2013-01-21 10:22:37 +0100

Jamie Gabe gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-20 20:37:08.950391

2 Answers

Sort by » oldest newest most voted

answered 2013-01-21 17:56:08 +0100

w_whalley gravatar image

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

edit flag offensive delete link more


Thank you very much!

Jamie Gabe gravatar imageJamie Gabe ( 2013-01-22 13:29:43 +0100 )edit

answered 2013-01-22 01:10:28 +0100

m.a.riosv gravatar image

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. TransformRecords_MultipleLine2OneLineRecord.ods

edit flag offensive delete link more

Question Tools


Asked: 2013-01-21 10:22:37 +0100

Seen: 192 times

Last updated: Jan 22 '13