Hi All
I have a very large dataset that is given like this
[TABLE="width: 2216"]
<colgroup><col><col><col span="2"><col><col><col span="4"><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Manager[/TD]
[TD]Trainer[/TD]
[TD]Department[/TD]
[TD]Start Date[/TD]
[TD="colspan: 6"]System 1[/TD]
[TD="colspan: 6"]System 2[/TD]
[/TR]
[TR]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[TD]Score [/TD]
[TD]Passed/Failed[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[TD]Score [/TD]
[TD]Passed/Failed[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]80[/TD]
[TD]Failed[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]67[/TD]
[TD]Failed[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Luke Fitzroy-Smith[/TD]
[TD]Mo Clay[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]95[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jessica Handley[/TD]
[TD]Alison Blood / Tom Peel[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Row 1 has the system name (Column F, then L) depending on how many systems they are etc as its merged
Row 2 has the field names
Row 3 to end row has the data
I need to transpose all this data so it looks like this
[TABLE="width: 1170"]
<colgroup><col><col span="3"><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Manager[/TD]
[TD]Trainer[/TD]
[TD]Department[/TD]
[TD]Start Date[/TD]
[TD]Metric[/TD]
[TD]Attempt[/TD]
[TD]Type[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 1[/TD]
[TD]80[/TD]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]2nd Attempt[/TD]
[TD]System 1[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 2[/TD]
[TD]67[/TD]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]2nd Attempt[/TD]
[TD]System 2[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Luke Fitzroy-Smith[/TD]
[TD]Mo Clay[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 1[/TD]
[TD]95[/TD]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Luke Fitzroy-Smith[/TD]
[TD]Mo Clay[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 2[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[/TR]
</tbody>[/TABLE]
Here is the scenario
I have a list of agents where they have different systems they are being tested on (i have 2 sheets to loop through but each sheet could differ in terms of how many systems they are being tested on)
Each agent has 3 attempts to pass per system (some may take 1 attempt, some 2 and some 3)
I need to list and transpose the data so it appears like above (Where i have put sheet name is going to be the sheet name i am currently looping through
I hope this makes sense
I have a very large dataset that is given like this
[TABLE="width: 2216"]
<colgroup><col><col><col span="2"><col><col><col span="4"><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Manager[/TD]
[TD]Trainer[/TD]
[TD]Department[/TD]
[TD]Start Date[/TD]
[TD="colspan: 6"]System 1[/TD]
[TD="colspan: 6"]System 2[/TD]
[/TR]
[TR]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[TD]Score [/TD]
[TD]Passed/Failed[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[TD]Score [/TD]
[TD]Passed/Failed[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]80[/TD]
[TD]Failed[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]67[/TD]
[TD]Failed[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Luke Fitzroy-Smith[/TD]
[TD]Mo Clay[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]95[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jessica Handley[/TD]
[TD]Alison Blood / Tom Peel[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Row 1 has the system name (Column F, then L) depending on how many systems they are etc as its merged
Row 2 has the field names
Row 3 to end row has the data
I need to transpose all this data so it looks like this
[TABLE="width: 1170"]
<colgroup><col><col span="3"><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Manager[/TD]
[TD]Trainer[/TD]
[TD]Department[/TD]
[TD]Start Date[/TD]
[TD]Metric[/TD]
[TD]Attempt[/TD]
[TD]Type[/TD]
[TD]Score[/TD]
[TD]Passed/Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 1[/TD]
[TD]80[/TD]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]2nd Attempt[/TD]
[TD]System 1[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 2[/TD]
[TD]67[/TD]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Elizabeth Bayes[/TD]
[TD]Louise Raisin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]2nd Attempt[/TD]
[TD]System 2[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Luke Fitzroy-Smith[/TD]
[TD]Mo Clay[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 1[/TD]
[TD]95[/TD]
[TD]Passed[/TD]
[/TR]
[TR]
[TD]Luke Fitzroy-Smith[/TD]
[TD]Mo Clay[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10/10/2016[/TD]
[TD]Sheet Name[/TD]
[TD]1stAttempt[/TD]
[TD]System 2[/TD]
[TD]100[/TD]
[TD]Passed[/TD]
[/TR]
</tbody>[/TABLE]
Here is the scenario
I have a list of agents where they have different systems they are being tested on (i have 2 sheets to loop through but each sheet could differ in terms of how many systems they are being tested on)
Each agent has 3 attempts to pass per system (some may take 1 attempt, some 2 and some 3)
I need to list and transpose the data so it appears like above (Where i have put sheet name is going to be the sheet name i am currently looping through
I hope this makes sense