Hi All,

I have a spreadsheet containing students' grades in this format:

M Wakefield A C D
A Sky F E B


What I would like to generate from the data above is this:


M Wakefield A
M Wakefield C
M Wakefield D
A Sky F
A Sky E
A Sky B



I have about 2000 entries, any help will be much appreciated.



Terapixels

Hi,
Data from Sheet1 - starting in col A, Row 2 - is copied to Sheet2:


HTH

Sub Transform()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long, i As Integer

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

rr = 2
ws2.Range("A1:B1") = Array("Student", "Grade")
With ws1
For r = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To .Cells(r, Columns.Count).End(xlToLeft).Column
.Cells(r, 1).Copy ws2.Cells(rr, 1)
.Cells(r, i).Copy ws2.Cells(rr, 2)
rr = rr + 1
Next i
Next r
End With

End Sub

"eclipse" wrote:

Hi All,

I have a spreadsheet containing students' grades in this format:

M Wakefield A C D
A Sky F E B


What I would like to generate from the data above is this:


M Wakefield A
M Wakefield C
M Wakefield D
A Sky F
A Sky E
A Sky B



I have about 2000 entries, any help will be much appreciated.



Terapixels