Compiling data from a training report

M

magpie2000k

Board Regular
Joined
Sep 13, 2013
Messages
196
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
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Last edited:
Upvote 0
Code: 
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
 
Last edited:
Upvote 0
Does you're data look like this


Excel 2013 32 bit
ABCDEFGHIJKLMNOP
1StudentCourse oneDate startedno. attemptsmark acheiveddate completedCourse 2Date startedno. attemptsmark acheiveddate completedCourse threeDate startedno. attemptsmark acheiveddate completed
2bob smithfirst aid01/05/2018165%02/05/2018fire safety01/05/2018165%02/05/2018
3John Doefirst aid02/05/2018165%03/05/2018Health & safety02/05/2018165%03/05/2018
4Jane Smithfirst aid03/05/2018165%04/05/2018
5Frank Sinatrafirst aid04/05/2018165%05/05/2018Health & safety04/05/2018165%05/05/2018fire safety04/05/2018165%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
 
Last edited:
Upvote 0
Fluff said:
Does you're data look like this

Excel 2013 32 bit
ABCDEFGHIJKLMNOP
StudentCourse oneDate startedno. attemptsmark acheiveddate completedCourse 2Date startedno. attemptsmark acheiveddate completedCourse threeDate startedno. attemptsmark acheiveddate completed
bob smithfirst aidfire safety
John Doefirst aidHealth & safety
Jane Smithfirst aid
Frank Sinatrafirst aidHealth & safetyfire 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
Click to expand...

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
 
Last edited:
Upvote 0
Ok, add this line of code as shown
Code: 
   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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Can you beat chat GPT on this question
2 3
Replies
29
Views
2K
Anthony47
A
D
  • Question Question
Find the matching details from 2 workbooks compile it in a sheet and then summarize it and put it in different sheet.
Replies
0
Views
385
deba2020
D
R
  • Question Question
Managing Expiry Dates
Replies
3
Views
2K
Nvablejrg
N
D
  • Question Question
VBA to Populate an Issue Status Cell
Replies
0
Views
381
Desiato34
D
aba1csims
  • Question Question
Need a VBA script to pull most recent training dates
Replies
4
Views
754
aba1csims
aba1csims

Forum statistics

Threads
1,223,706
Messages
6,173,998
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top