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

 

[JPARKHURST]

Is this a new or old spreadsheet?

If I may, I believe you need a restructurign of your data. This is a common 'corner' to get around.

The columns in excel may be used to seperate out *types* of data.
The rows are data entry points.

In this example, we are re-stating several types of data in new columns. Course 1, Course 2 are equal and the same - the amount of data recorded for each one is the same.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[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][TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD]Date started[/TD]
[TD]no. attempts[/TD]
[TD]mark acheived[/TD]
[TD]date completed[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table"]
<tbody>[TR]
[TD]Course three[/TD]
[TD]Date started[/TD]
[TD]no. attempts[/TD]
[TD]mark acheived[/TD]
[TD]date completed[/TD]
[/TR]
</tbody>[/TABLE]

[/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]
[/TR]
</tbody>[/TABLE]
[\quote]


A Course has this:
Title (1,2,2) Start Date, Number of Attempts, Mark, Date Completed.

A row will contain all of the info about the class, as well as what student this is referring to (do you have a student number, I hope, it would be easier to track similar / same names). The actual information (changing start times, class name, etc) will determine what the record means, and we can separate them out in whatever form of report we wish to do. Table, data, export, etc.

I would further suggest altering the No. Attempts to Attempt Number, allowing you to capture *each* instance of the class taken by a student.They can later be averaged or the latest entry used.

With that being said, new table structure:

Student ID (Name or Number)    |    Course    |    Attempt Number    |    Date Started    |    Date Completed    |    Mark Received

Re-reading this, I'm not sure what Course1 designates vs. Course2? Is it that they may have X number of courses in a time frame? We may add an additional tracking feature to your columns, which is Course Status - Planned, In Progress, Completed.


What do you think of that?

HTH,

Jon

 

[magpie2000k]

Hi and thanks.

Unfortunately this is an export and I am attempting to do pretty much what you are saying from the export.

But I have the data in that format as it stands and want to get to where you are stating.

Hope that makes sense. for the record there are approx 100 courses accross the columns I know it is a mess but it is what I have to work with and neeed to tidy it up so it is at all readale

cheers

Jon

 

[JPARKHURST]

nasty! OK, so am I correct in assuming the data set (# of columns) is the same for all courses?

Are all courses laid out with headers at the top only, and students listed below?

Is this tracking only one year or multiple years?

Can you use a macro, or are we dealing with formulas alone?

 

[magpie2000k]

Ok I am happy with what ever way to get the data in a readable format. as it will only be used to to that.. (yes I know built by the most ilogical person in the world i guess)
Multiple years
All headers are at the top only.

Hope you can help as it is a beep beep beep ache

Yes set number of columns for whole export

Jon

 

[JPARKHURST]

Is this tracking only one year or multiple years?

Really just wantint to know how large our dataset is. Is this recording one time period or ongoing?

 

[JPARKHURST]

OK, excellent. That makes everything much easier.

Just a few, meeting, will get back to you around lunch. (2 hrs or so)

Thanks,

Jon

 

[magpie2000k]

Ok when I export it it will have a variable number of columns. Up to a max of 600, 5 columns per course.

It has records from the past but will not go further ahead if that makes sense.. this will be a one time exercise to get our data in order and load into new database

Hope that makes sense

Jon

 

[magpie2000k]

one further thing where a cell is in my words empty it actually has a - in it

 

[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("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("Sheet1").Range("A1:E1").Value = Array("Student", "Course", "Attepmts", "Pass Mark", "Date Achieved")
   Sheets("Sheet1").Range("A2").Resize(j - 1, 5).Value = Application.Transpose(Nary)
End Sub