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]

Sorry but there isnt I must be doing somethign else wrong i get

Microsoft visual basic for applications box

a yellow triangle with an explamtion mark in it

Then run time error '9':
subscript out of range

I can then press only OK or HELP

Ok closes dialogue box
Help takes me to a web site

 

[magpie2000k]

I think it is me


below are the actual titles for first 7 columns

Learner

<tbody>
[TD="width: 189"]Office[/TD]
[TD="width: 117"]Course one[/TD]
[TD="width: 111"]Date Assigned[/TD]
[TD="width: 64"]# Attempts[/TD]
[TD="width: 64"]Pass Mark[/TD]
[TD="width: 111"]Date Completed[/TD]

</tbody>

After this it repeats

Course 2

<tbody>
[TD="width: 64"]Date Assigned[/TD]
[TD="width: 64"]# Attempts[/TD]
[TD="width: 64"]Pass Mark[/TD]
[TD="width: 64"]Date Completed[/TD]

</tbody>

<tbody>
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

</tbody>

The course name changes above is course 2 but it will be actual name of course

<tbody>
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

</tbody>

<tbody>
[TD="width: 189"][/TD]
[TD="width: 117"][/TD]
[TD="width: 111"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 111"][/TD]

</tbody>

<tbody>
</tbody>

guessing trying to explain I gave vague terms for things but in actual fact you probably need facts.

 

[magpie2000k]

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

Nary(i, j) = ary(r, c + k) is error on debug

 

[Fluff]

Does you're data look like this

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Student	Course one	Date started	no. attempts	mark acheived	date completed	Course 2	Date started	no. attempts	mark acheived	date completed	Course three	Date started	no. attempts	mark acheived	date completed
2	bob smith	first aid	01/05/2018	1	65%	02/05/2018						fire safety	01/05/2018	1	65%	02/05/2018
3	John Doe	first aid	02/05/2018	1	65%	03/05/2018	Health & safety	02/05/2018	1	65%	03/05/2018
4	Jane Smith	first aid	03/05/2018	1	65%	04/05/2018
5	Frank Sinatra	first aid	04/05/2018	1	65%	05/05/2018	Health & safety	04/05/2018	1	65%	05/05/2018	fire safety	04/05/2018	1	65%	05/05/2018
Summary

If not can you please post some representative data. There are tools available here to help you do that & a test board here where you can try them out

 

[magpie2000k]

Does you're data look like this

Excel 2013 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Student	Course one	Date started	no. attempts	mark acheived	date completed	Course 2	Date started	no. attempts	mark acheived	date completed	Course three	Date started	no. attempts	mark acheived	date completed
bob smith	first aid	fire safety
John Doe	first aid	Health & safety
Jane Smith	first aid
Frank Sinatra	first aid	Health & safety	fire safety

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]02/05/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]02/05/2018[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]02/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]03/05/2018[/TD]

[TD="align: right"]02/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]03/05/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]03/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]04/05/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]04/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]05/05/2018[/TD]

[TD="align: right"]04/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]05/05/2018[/TD]

[TD="align: right"]04/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]05/05/2018[/TD]

</tbody>

Summary​

If not can you please post some representative data. There are tools available here to help you do that & a test board here where you can try them out

Almost exactly But empty cells have a - in them if that makes sense.. In simple terms it is a report but we need to be able to pull data from the report more easily to report on it.

Instead of course one course two etc it is teh exact name of the course

The no of attempts is #Attempts



Thanks

 

[Fluff]

Ok, add this line of code as shown

   ary = Sheets("Data").Range("A1").CurrentRegion.Offset(1)
  [COLOR=#0000ff] MsgBox UBound(ary, 1) & vbLf & UBound(ary, 2)[/COLOR]
   j = 1

What does the message box say?.
And when the macro crashes what are the values of i,j,r,c &k?

 

[magpie2000k]

Added line as shown

Run-time error'9':
subscript out of range

I hit debug and it just highlights Nary(i, j) = ary(r, c + k) in yellow

I dont see where I get you the values

Got it
39
227

I get a box pop up on the data spread sheet

Just says Microsoft excel as title

and has
39
227

 

[magpie2000k]

I am running this on a sample of data to get it working dont know if that matters but there are only 39 rows just thought I would say

We will have multiple exports that has different amounts of rows and columns in them

sorry if I confuse things at all just trying to tell you everything.

Some exports will be much bigger

 

[Fluff]

Would you be willing to upload your test file to a share site such as OneDrive, DropBox, Google, mark for sharing & post the link to this thread?

 

[magpie2000k]

I can but dont want everyone to be able to get at it.