Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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