Extracting Exam data from Broadsheet

[dpbarry]

Hi Folks

I have an exam spreadsheet which has everything in columns - Surname, forename, exam number and then a list of subjects (26 in total) in the form of:

Forename

Surname

Reg

Exam

Art

Biology

Business St

Chemistry

Computing

Drama

English

French

Further Maths

Geography

Jane

Doe

14A

5055

B​

B

B

Sarah

Payne

14D

5058

A

A

A

Orla

Briggs

14C

5059

B

A

A

The are 101 Rows (number of students) and each student could have received a grade for 1 to 6 subjects

What a teacher would like - if possible - is for the data to be stripped down and only showing grades a student received but to be presented in the form of

Forename	Surname	Subject	Grade
Jane	Doe	Chemistry	B
Jane	Doe	French	B
Jane	Doe	Geography	B
Sarah	Payne	Art	A
Sarah	Payne	Business St	A
Sarah	Payne	Computing	A
Orla	Briggs	Biology	B
Orla	Briggs	Computing	A
Orla	Briggs	French	A

I'm currently using Microsoft Excel 2016 and any help would be greatly appreciated.

Regards

Declan

 

[Fluff]

I'm currently using Microsoft Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

Sub dpbarry()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * 26, 1 To 4)
   
   For r = 2 To UBound(Ary)
      For c = 5 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, 2)
            Nary(nr, 3) = Ary(1, c)
            Nary(nr, 4) = Ary(r, c)
         End If
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 4).Value = Nary
End Sub

Change sheet names to suit.

 

[dpbarry]

Fluff.

You are an Absolute Star.

The teacher will be chuffed to bits and it will probably earn me brownie points but I'll be letting the teacher know that it was all your work.

Greatly appreciated

Regards

Declan

 

[Fluff]

You're welcome & thanks for the feedback.

 

[dpbarry]

You're welcome & thanks for the feedback.

Its great that you solved my query so quickly but I'm also intrigued as to what the code is actually doing. Could you elaborate on the code??

Regards

Declan