Compiling data from a training report

[magpie2000k]

Hi I wonder if you can help me I have a spread sheet of training for my staff

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student
[/TD]
[TD]Course one
[/TD]
[TD]Date started
[/TD]
[TD]no. attempts
[/TD]
[TD]mark acheived
[/TD]
[TD]date completed
[/TD]
[TD]Course 2
[/TD]
[TD]

Date started

no. attempts

mark acheived

date completed

<tbody>
</tbody>

[/TD]
[TD]

Course three

Date started

no. attempts

mark acheived

date completed

<tbody>
</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob smith
[/TD]
[TD]first aid
[/TD]
[TD]01/05/2018
[/TD]
[TD]1
[/TD]
[TD]65%
[/TD]
[TD]02/05/2018
[/TD]
[TD]fire safety
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The columns go on and on for multiple courses
I basically need to pull a summary for the learner

However as the columns go across the page they may or may not have taken each course

so in example above bob may have taken course on but not course 2 or three but may have taken course 4 (not shown)

What I am hoping to acheive isthe following

[TABLE="width: 500"]
<tbody>[TR]
[TD]Delegate
[/TD]
[TD]Course
[/TD]
[TD]Attempts
[/TD]
[TD]Pass mark
[/TD]
[TD]Date achieved
[/TD]
[/TR]
[TR]
[TD]Bob Smith
[/TD]
[TD]First aid
[/TD]
[TD]1
[/TD]
[TD]65%
[/TD]
[TD]02/05/2018
[/TD]
[/TR]
[TR]
[TD]Bob Smith
[/TD]
[TD]Fire
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob Smith
[/TD]
[TD]Course 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob Smith
[/TD]
[TD]Course 4
[/TD]
[TD]2
[/TD]
[TD]75%
[/TD]
[TD]03/05/2018
[/TD]
[/TR]
</tbody>[/TABLE]

I hope that makes some rough sense. I am more looking at the general way of doing it then I should be able to adapt the formulas.

Thanks

 

[magpie2000k]

Runtime error '9'
Subscript out of range

Did I do somethign wrong I am not good at macros but can follow instructions lol

 

[Fluff]

Did you change the sheet names to match your sheet names?

 

[magpie2000k]

as siad not good at this lol..

just teh red ones

Sub ReorganiseData()
   Dim r As Long, c As Long, i As Long, j As Long, k As Long
   Dim ary As Variant
   Dim Nary() As Variant
   
   ary = Sheets("Summary").Range("A1").CurrentRegion.Offset(1)
   j = 1
   For r = 1 To UBound(ary)
      For c = 2 To UBound(ary, 2) Step 5
         If Not ary(r, c) = "-" Then
            ReDim Preserve Nary(1 To 5, 1 To j)
            Nary(1, j) = ary(r, 1)
            For i = 2 To 5
               Nary(i, j) = ary(r, c + k)
               If k = 0 Then k = 2 Else k = k + 1
            Next i
            j = j + 1: k = 0
         End If
      Next c
   Next r
   Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A1:E1").Value = Array("Student", "Course", "Attepmts", "Pass Mark", "Date Achieved")
   Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A2").Resize(j - 1, 5).Value = Application.Transpose(Nary)
End Sub

 

[Fluff]

What about this one

ary = Sheets("[COLOR=#ff0000]Summary[/COLOR]").Range("A1").CurrentRegion.Offset(1)

Also when posting code please use code tags, the # icon in the reply window

 

[magpie2000k]

sorry for being thick

Data is in a sheet names data
I want to output to sheet named result

so I change summary to "result" and the bottom two sheets to "data"

I do this and get

run-time error-13
type mismatch

 

[Fluff]

You need to change them the other way.

Sub ReorganiseData()
   Dim r As Long, c As Long, i As Long, j As Long, k As Long
   Dim ary As Variant
   Dim Nary() As Variant
   
   ary = Sheets("Data").Range("A1").CurrentRegion.Offset(1)
   j = 1
   For r = 1 To UBound(ary)
      For c = 2 To UBound(ary, 2) Step 5
         If Not ary(r, c) = "-" Then
            ReDim Preserve Nary(1 To 5, 1 To j)
            Nary(1, j) = ary(r, 1)
            For i = 2 To 5
               Nary(i, j) = ary(r, c + k)
               If k = 0 Then k = 2 Else k = k + 1
            Next i
            j = j + 1: k = 0
         End If
      Next c
   Next r
   Sheets("Result").Range("A1:E1").Value = Array("Student", "Course", "Attepmts", "Pass Mark", "Date Achieved")
   Sheets("Result").Range("A2").Resize(j - 1, 5).Value = Application.Transpose(Nary)
End Sub

 

[magpie2000k]

sorry idiots guide must be needed I have done this but still get error 9 subscript out of range

In project-vbaProject

VBA project (jonners export.csv)
-microsoft excel objects
-Sheet1(data)
-sheet 2 (result
This workbook

where do I put the code

 

[Fluff]

Which line of code is highlighted when you click Debug?

 

[magpie2000k]

I must sound stupid but I dont know debug gives me a drop down menu

step into run cursor etc sorry my knowledge of vba is very little

 

[Fluff]

When you run the code & it gives you the error message. There is a button on that message box called Debug. If you click-it then a row of code will be highlighted in yellow. Which one?