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]

As it's a new question, best if you start a new thread.

 

[magpie2000k]

Fluff you helped me with this ages ago and I have been asked to add something to it..
Thought easier to ask back on this thread as it might help you.. I get you will speak to hundreds of us...

Basically I have it pulling accross the course names. If I have a list of course names saved on say sheet (course names)
with the name they are called on teh export, can I have it update the course name in the results with new course name

Thanks in advance

 

[Fluff]

Can you please start a new thread. Thanks