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

 

[Fluff]

The board policy is that everything is to be kept on the board & so files cannot be shared privately.
If you are unwilling to share the file publicly, we'll just have to try & debug it long distance.

 

[magpie2000k]

The board policy is that everything is to be kept on the board & so files cannot be shared privately.
If you are unwilling to share the file publicly, we'll just have to try & debug it long distance.

https://www.dropbox.com/s/abo11dbvj3a721k/jonners export.xlsm?dl=0

Hi I have deleted any personal info ie names and office locations so shouldnt be anything on this now

 

[Fluff]

Ok, You've got an extra column in there that's causing the problem.
If you don't want to keep the Office try

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 = 3 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("A7:E7").Value = Array("Student", "Course", "Attepmts", "Pass Mark", "Date Achieved")
   Sheets("Result").Range("A8").Resize(j - 1, 5).Value = Application.Transpose(Nary)
End Sub

If you want the office let me know. Also if "date Assigned" is "Blank" do you want that course excluded from the result?

 

[magpie2000k]

if date assigned is blank then yes exclude means they havent had teh course to complete

Yes please keep office if not too much trouble as there can be multiple entries in that

would like office in the results...

hope that makes sense

In results can we add date asigned column

 

[Fluff]

How about

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 = 3 To UBound(ary, 2) Step 5
         If Not ary(r, c + 1) = "-" Then
            ReDim Preserve Nary(1 To 7, 1 To j)
            Nary(1, j) = ary(r, 1)
            Nary(2, j) = ary(r, 2)
            For i = 3 To 7
               Nary(i, j) = ary(r, c + k)
               k = k + 1
            Next i
            j = j + 1: k = 0
         End If
      Next c
   Next r
   Sheets("Result").Range("A1:G1").Value = Array("Student", "Office", "Course", "Date Assigned", "Attepmts", "Pass Mark", "Date Achieved")
   Sheets("Result").Range("A2").Resize(j - 1, 7).Value = Application.Transpose(Nary)
End Sub

 

[magpie2000k]

Fluff you are and absolute genuis.......

Cant thank you enough

 

[Fluff]

Glad to help & thanks for the feedback

 

[magpie2000k]

Just so I know can a macro look at two spear sheets at same time.
Believe it or not the system to pull the reports from allows you to pull a user form also ie

Name office email address telephone number user name and password.

Would it be possible for the code to add those fields into the report you just created if they were indeed in a seperate tabe on the same workbook

 

[Fluff]

Yes that should be possible.

 

[magpie2000k]

great if I need it should i pop you a message or just update this thread. just to help you remember where you got to.. Might not need it but may do


Thanks again for everything