Complicated transpose of data with conditions

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I have this data set that i need to transpose but i dont know exactly where to start to explain what im trying to do so i will try my best to explain

I have a data set that looks like this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>AI</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Respondent ID</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Collector ID</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Start Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">End Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">IP Address</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Email Address</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">First Name</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Last Name</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Custom Data 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Please enter the Unique Reference Number of the training you attended (you can copy & paste this from the email you have received)</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Area</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Please enter the programme name of the training you attended (you can copy & paste this from the email you have received)</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Please select from the drop down menu the lead trainer for the session</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Your name:</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Your base site:</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">For each of the statements below, let us know how strongly you agree with the statement, where 1 = strongly disagree and 5 = strongly agree</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: #333333;background-color: #EAEAE8;;">How could we improve the invitation for you next time?</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">For each of the statements below, let us know how strongly you agree with the statement, where 1 = strongly disagree and 5 = strongly agree</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">How could we improve the training event for you next time?</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">For each of the statements below, let us know how strongly you agree with the statement, where 1 = strongly disagree and 5 = strongly agree</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">How could we improve the environment/resources for you next time?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Open-Ended Response</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Open-Ended Response</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Response</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Other (please specify)</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Open-Ended Response</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Response</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Other (please specify)</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I received an invitation with sufficient notice</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">The invitation was inviting and engaging</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I was clear about venues, dates and timings</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I was clear about the purpose of the training</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I understood why I had been invited</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">The preparation made sense given the purpose</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: #333333;background-color: #EAEAE8;;">Open-Ended Response</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I was fully engaged throughout the training event</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I felt able to ask questions and ask for help</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">The practical sessions helped bring the theory to life</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">The pace of the training worked for me</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I am confident I can apply my learning</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">I am clear about the support I can expect after the training event</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Open-Ended Response</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">The training space, equipment & materials all worked for me</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">The group size worked well</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">The duration of the training was just right given what we were learning</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #EAEAE8;;">Open-Ended Response</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;;">test</td><td style="border-top: 1px solid black;;">test</td><td style="text-align: right;border-top: 1px solid black;;">2018-05-17 16:33:43</td><td style="text-align: right;border-top: 1px solid black;;">2018-05-17 16:35:43</td><td style="border-top: 1px solid black;;">test</td><td style="border-top: 1px solid black;;">test</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;">102-TR-May-18-7-TM1</td><td style="border-top: 1px solid black;;">TM1</td><td style="border-top: 1px solid black;;">PROGRAMME1</td><td style="border-top: 1px solid black;;">Name1</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;">Name 1</td><td style="border-top: 1px solid black;;">Other (please specify)</td><td style="border-top: 1px solid black;;">Lake House</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="border-top: 1px solid black;font-style: italic;;">TEST</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="border-top: 1px solid black;;">TEST</td><td style="text-align: right;border-top: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;;">4</td><td style="border-top: 1px solid black;;">TEST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 16:09:18</td><td style="text-align: right;;">2018-05-17 16:23:53</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 2</td><td style=";">Derby</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">TEST</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">TEST</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">TEST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 14:55:22</td><td style="text-align: right;;">2018-05-17 14:57:15</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 3</td><td style=";">London</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">...................</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style=";">TEST</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style=";">TEST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 14:23:45</td><td style="text-align: right;;">2018-05-17 14:25:39</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 4</td><td style=";">London</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style=";">N/A</td><td style=";">TEST</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style=";">TEST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 14:01:01</td><td style="text-align: right;;">2018-05-17 14:02:54</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME2</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 5</td><td style=";">Other (please specify)</td><td style=";">na</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style=";">TEST</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style=";">TEST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 13:46:26</td><td style="text-align: right;;">2018-05-17 13:48:17</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME2</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 6</td><td style=";">Manchester</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style=";">TEST</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">TEST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 13:10:12</td><td style="text-align: right;;">2018-05-17 13:12:02</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME2</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 7</td><td style=";">Manchester</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style=";">TEST</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style=";">TEST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 13:07:45</td><td style="text-align: right;;">2018-05-17 13:11:25</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME2</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 8</td><td style=";">Derby</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">N/A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style=";">NA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-17 13:05:46</td><td style="text-align: right;;">2018-05-17 13:09:22</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME2</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 9</td><td style=";">Manchester</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">NA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-14 14:31:51</td><td style="text-align: right;;">2018-05-14 14:53:40</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">95-TR-May-18-5-TM2</td><td style=";">TM2</td><td style=";">PROGRAMME3</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 10</td><td style=";">Manchester</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style=";">N/A</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style=";">NA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-11 15:08:44</td><td style="text-align: right;;">2018-05-11 15:15:42</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">95-TR-May-18-5-TM2</td><td style=";">TM2</td><td style=";">PROGRAMME3</td><td style=";">Name1</td><td style="text-align: right;;"></td><td style=";">Name 11</td><td style=";">London</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">N/A</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">N/A</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style=";">NA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-11 15:06:21</td><td style="text-align: right;;">2018-05-11 15:08:36</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">95-TR-May-18-5-TM2</td><td style=";">TM2</td><td style=";">PROGRAMME3</td><td style=";">Name 2</td><td style="text-align: right;;"></td><td style=";">Name 12</td><td style=";">Manchester</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style=";">N/A</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style=";">NA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-11 13:53:40</td><td style="text-align: right;;">2018-05-11 13:56:30</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">95-TR-May-18-5-TM2</td><td style=";">TM2</td><td style=";">PROGRAMME3</td><td style=";">Name 3</td><td style="text-align: right;;"></td><td style=";">Name 13</td><td style=";">London</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style=";">N/A</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style=";">NA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;">2018-05-11 13:53:38</td><td style="text-align: right;;">2018-05-11 13:57:32</td><td style=";">test</td><td style=";">test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">95-TR-May-18-5-TM2</td><td style=";">TM2</td><td style=";">PROGRAMME3</td><td style=";">Name 1</td><td style="text-align: right;;"></td><td style=";">Name 14</td><td style=";">London</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="font-style: italic;;">NA</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style=";">N/A</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style=";">NA</td></tr></tbody></table><p style="width:12.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Learner 1(<2hrs)</p><br /><br />

I want to transpose the data so all the questions are split out on individual rows in order for me to be able to slice and use in my pivot table

Output like this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Respondent ID</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Name</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Date</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Type</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Section</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">URN</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Area</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Programme Name</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Lead Trainer Name</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Base Site</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Question</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #0070C0;;">Rating</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">INVITATION</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style=";">I received an invitation with sufficient notice</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">INVITATION</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;;">I was clear about the purpose of the training</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">INVITATION</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;;">I was clear about venues, dates and timings</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">INVITATION</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;;">The invitation was inviting and engaging</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">INVITATION</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;;">The preparation made sense given the purpose</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">INVITATION</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;;">I understood why I had been invited</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">TRAINING EVENT</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;;">I was fully engaged throughout the training event</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">TRAINING EVENT</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;;">I felt able to ask questions and ask for help </td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">test</td><td style=";">Name 1</td><td style="text-align: right;;">17/05/2018</td><td style=";">Learner 1(<2hrs)</td><td style="text-align: center;;">TRAINING EVENT</td><td style=";">102-TR-May-18-7-TM1</td><td style=";">TM1</td><td style=";">PROGRAMME1</td><td style=";">Name1</td><td style=";">Lake House</td><td style="text-align: center;border-bottom: 1px solid black;;">The practical sessions helped bring the theory to life</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Output</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">Respondent ID</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">Name</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">Date</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">Type</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">Section</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F1</th><td style="text-align:left">URN</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G1</th><td style="text-align:left">Area</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H1</th><td style="text-align:left">Programme Name</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I1</th><td style="text-align:left">Lead Trainer Name</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J1</th><td style="text-align:left">Base Site</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K1</th><td style="text-align:left">Question</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L1</th><td style="text-align:left">Rating</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">INVITATION</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">I received an invitation with sufficient notice</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">INVITATION</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J3</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K3</th><td style="text-align:left">I was clear about the purpose of the training</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L3</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A4</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E4</th><td style="text-align:left">INVITATION</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F4</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G4</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H4</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J4</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K4</th><td style="text-align:left">I was clear about venues, dates and timings</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L4</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">INVITATION</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F5</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G5</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H5</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J5</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K5</th><td style="text-align:left">The invitation was inviting and engaging</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L5</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A6</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E6</th><td style="text-align:left">INVITATION</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F6</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G6</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H6</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I6</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J6</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K6</th><td style="text-align:left">The preparation made sense given the purpose</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L6</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A7</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C7</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D7</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E7</th><td style="text-align:left">INVITATION</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F7</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G7</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H7</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I7</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J7</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K7</th><td style="text-align:left">I understood why I had been invited</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L7</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A8</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C8</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D8</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E8</th><td style="text-align:left">TRAINING EVENT</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F8</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G8</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H8</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I8</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J8</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K8</th><td style="text-align:left">I was fully engaged throughout the training event</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L8</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A9</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D9</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E9</th><td style="text-align:left">TRAINING EVENT</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F9</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G9</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H9</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I9</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J9</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K9</th><td style="text-align:left">I felt able to ask questions and ask for help </td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L9</th><td style="text-align:left">4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A10</th><td style="text-align:left">test</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">Name 1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C10</th><td style="text-align:left">43237</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D10</th><td style="text-align:left">Learner 1(<font color="Blue"><2hrs</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E10</th><td style="text-align:left">TRAINING EVENT</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F10</th><td style="text-align:left">102-TR-May-18-7-TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G10</th><td style="text-align:left">TM1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H10</th><td style="text-align:left">PROGRAMME1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I10</th><td style="text-align:left">Name1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J10</th><td style="text-align:left">Lake House</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K10</th><td style="text-align:left">The practical sessions helped bring the theory to life</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L10</th><td style="text-align:left">4</td></tr></tbody></table></td></tr></table><br />

These are the criterias

I have 5 sheets to look at in my workbook however each sheet does not have the data in the same columns
The Header is in row1 and any questions appear to be in row 2
All the output - results appear to start from row 3
The questions that in row 2 also have the word Open Response - those questions can be ignored and not required
Each section has different amount questions

I have a list of questions for each type and section - I need to look at the range of questions in my look up table and then match it in the Raw Data table and get the result of that question (All the questions should be in the RAW DATA however if for whatever reason it aint then we can look at the next question)

The Question Lookup range looks like this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;text-align: center;;"># OF QUESTIONS</td><td style="font-weight: bold;text-align: center;;">TYPE</td><td style="font-weight: bold;text-align: center;;">SECTION</td><td style="font-weight: bold;text-align: center;;">Q1</td><td style="font-weight: bold;text-align: center;;">Q2</td><td style="font-weight: bold;text-align: center;;">Q3</td><td style="font-weight: bold;text-align: center;;">Q4</td><td style="font-weight: bold;text-align: center;;">Q5</td><td style="font-weight: bold;text-align: center;;">Q6</td><td style="font-weight: bold;text-align: center;;">Q7</td><td style="font-weight: bold;text-align: center;;">Q8</td><td style="font-weight: bold;text-align: center;;">Q9</td><td style="font-weight: bold;text-align: center;;">Q10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;text-align: center;color: #C00000;;">6</td><td style="text-align: center;;">Learner 1 (<2hrs)</td><td style="text-align: center;;">INVITATION</td><td style="text-align: center;;">I received an invitation with sufficient notice</td><td style="text-align: center;;">I was clear about the purpose of the training</td><td style="text-align: center;;">I was clear about venues, dates and timings</td><td style="text-align: center;;">The invitation was inviting and engaging</td><td style="text-align: center;;">The preparation made sense given the purpose</td><td style="text-align: center;;">I understood why I had been invited</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="font-weight: bold;text-align: center;color: #C00000;;">10</td><td style="text-align: center;;">Learner 1 (<2hrs)</td><td style="text-align: center;;">TRAINING EVENT</td><td style="text-align: center;;">I was fully engaged throughout the training event</td><td style="text-align: center;;">I had a clear view of the learning agenda & daily priorities</td><td style="text-align: center;;">I felt able to ask questions and ask for help </td><td style="text-align: center;;">The assessments helped me test my learning</td><td style="text-align: center;;">The practical sessions helped bring the theory to life</td><td style="text-align: center;;">The pace of the training worked for me</td><td style="text-align: center;;">I am excited about putting my learning into practice</td><td style="text-align: center;;">I am confident I will have the opportunity to put my learning into practice</td><td style="text-align: center;;">I am clear about the support I can expect after the training event</td><td style="text-align: center;;">I understand how this learning can help me be successful in my role</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;text-align: center;color: #C00000;;">6</td><td style="text-align: center;;">Learner 1 (<2hrs)</td><td style="text-align: center;;">THE LEARNING ENVIRONMENT & RESOURCES</td><td style="text-align: center;;">The training space worked for me</td><td style="text-align: center;;">We had all the equipment we needed and it always worked</td><td style="text-align: center;;">The slides, materials and handouts made learning easier</td><td style="text-align: center;;">I am confident I will use the handouts and notes to help me when I'm 'live'</td><td style="text-align: center;;">The group size worked well</td><td style="text-align: center;;">The duration of the training was just right given what we were learning</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="font-weight: bold;text-align: center;;">TYPE</td><td style="font-weight: bold;text-align: center;;">SECTION</td><td style="font-weight: bold;text-align: center;;">Q1</td><td style="font-weight: bold;text-align: center;;">Q2</td><td style="font-weight: bold;text-align: center;;">Q3</td><td style="font-weight: bold;text-align: center;;">Q4</td><td style="font-weight: bold;text-align: center;;">Q5</td><td style="font-weight: bold;text-align: center;;">Q6</td><td style="font-weight: bold;text-align: center;;">Q7</td><td style="font-weight: bold;text-align: center;;">Q8</td><td style="font-weight: bold;text-align: center;;">Q9</td><td style="font-weight: bold;text-align: center;;">Q10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="font-weight: bold;text-align: center;color: #C00000;;">3</td><td style="text-align: center;;">Learner 1 (>2hrs)</td><td style="text-align: center;;">INVITATION</td><td style="text-align: center;;">I received an invitation with sufficient notice and all info I needed</td><td style="text-align: center;;">The invitation was inviting and engaging</td><td style="text-align: center;;">I understood why I had been invited</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="font-weight: bold;text-align: center;color: #C00000;;">6</td><td style="text-align: center;;">Learner 1 (>2hrs)</td><td style="text-align: center;;">TRAINING EVENT</td><td style="text-align: center;;">I was fully engaged throughout the training event</td><td style="text-align: center;;">I felt able to ask questions and ask for help </td><td style="text-align: center;;">The practical sessions helped bring the theory to life</td><td style="text-align: center;;">The pace of the training worked for me</td><td style="text-align: center;;">I am confident I can apply my learning</td><td style="text-align: center;;">I am clear about the support I can expect after the training event</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="font-weight: bold;text-align: center;color: #C00000;;">3</td><td style="text-align: center;;">Learner 1 (>2hrs)</td><td style="text-align: center;;">THE LEARNING ENVIRONMENT & RESOURCES</td><td style="text-align: center;;">Training space, equipment and materials all worked for me</td><td style="text-align: center;;">The duration of the training was just right given what we were learning</td><td style="text-align: center;;">The group size worked for me</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="font-weight: bold;text-align: center;;">TYPE</td><td style="font-weight: bold;text-align: center;;">SECTION</td><td style="font-weight: bold;text-align: center;;">Q1</td><td style="font-weight: bold;text-align: center;;">Q2</td><td style="font-weight: bold;text-align: center;;">Q3</td><td style="font-weight: bold;text-align: center;;">Q4</td><td style="font-weight: bold;text-align: center;;">Q5</td><td style="font-weight: bold;text-align: center;;">Q6</td><td style="font-weight: bold;text-align: center;;">Q7</td><td style="font-weight: bold;text-align: center;;">Q8</td><td style="font-weight: bold;text-align: center;;">Q9</td><td style="font-weight: bold;text-align: center;;">Q10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="font-weight: bold;text-align: center;color: #C00000;;">4</td><td style="text-align: center;;">Learner 2</td><td style="text-align: center;;">SINCE THE TRAINING</td><td style="text-align: center;;">I have been able to put the learning into practice and my confidence is increasing as a result</td><td style="text-align: center;;">I have had the support I need to successfully apply what I learned</td><td style="text-align: center;;">The training has helped me perform better in my role</td><td style="text-align: center;;">The training will help me perform better in my role in the future</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="font-weight: bold;text-align: center;;">TYPE</td><td style="font-weight: bold;text-align: center;;">SECTION</td><td style="font-weight: bold;text-align: center;;">Q1</td><td style="font-weight: bold;text-align: center;;">Q2</td><td style="font-weight: bold;text-align: center;;">Q3</td><td style="font-weight: bold;text-align: center;;">Q4</td><td style="font-weight: bold;text-align: center;;">Q5</td><td style="font-weight: bold;text-align: center;;">Q6</td><td style="font-weight: bold;text-align: center;;">Q7</td><td style="font-weight: bold;text-align: center;;">Q8</td><td style="font-weight: bold;text-align: center;;">Q9</td><td style="font-weight: bold;text-align: center;;">Q10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="font-weight: bold;text-align: center;color: #C00000;;">3</td><td style="text-align: center;;">TL Feedback</td><td style="text-align: center;;">SET UP</td><td style="text-align: center;;">I was clear about the purpose of the training</td><td style="text-align: center;;">I had the right amount of contact and involvement with L&D in the run up</td><td style="text-align: center;;">I was happy with the design of the training and what it would deliver</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="font-weight: bold;text-align: center;color: #C00000;;">6</td><td style="text-align: center;;">TL Feedback</td><td style="text-align: center;;">TRAINING EVENT</td><td style="text-align: center;;">I fully understood what was expected of me during the training</td><td style="text-align: center;;">The training was well managed</td><td style="text-align: center;;">The training was delivered in an engaging and interesting way (personal experience)</td><td style="text-align: center;;">If N/A-didn't attend, branch to: The training was delivered in an engaging and interesting way (other's reports)</td><td style="text-align: center;;">I am satisfied with the updates and communication I received throughout</td><td style="text-align: center;;">I am satisfied with the capability of the Learners at this point</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="font-weight: bold;text-align: center;color: #C00000;;">5</td><td style="text-align: center;;">TL Feedback</td><td style="text-align: center;;">REINFORCEMENT</td><td style="text-align: center;;">I understood what I could expect from L&D in terms of supporting reinforcement</td><td style="text-align: center;;">I understood what my role was in reinforcing the learning</td><td style="text-align: center;;">I am satisfied with how L&D collaborated with me/us after the training</td><td style="text-align: center;;">The reinforcement went as planned</td><td style="text-align: center;;">I am satisfied that the reinforcement activity was successful</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="font-weight: bold;text-align: center;color: #C00000;;"></td><td style="font-weight: bold;text-align: center;;">TYPE</td><td style="font-weight: bold;text-align: center;;">SECTION</td><td style="font-weight: bold;text-align: center;;">Q1</td><td style="font-weight: bold;text-align: center;;">Q2</td><td style="font-weight: bold;text-align: center;;">Q3</td><td style="font-weight: bold;text-align: center;;">Q4</td><td style="font-weight: bold;text-align: center;;">Q5</td><td style="font-weight: bold;text-align: center;;">Q6</td><td style="font-weight: bold;text-align: center;;">Q7</td><td style="font-weight: bold;text-align: center;;">Q8</td><td style="font-weight: bold;text-align: center;;">Q9</td><td style="font-weight: bold;text-align: center;;">Q10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="font-weight: bold;text-align: center;color: #C00000;;">9</td><td style="text-align: center;;">Stake Feedback</td><td style="text-align: center;;">RESOURCE & CLIENT MANAGEMENT</td><td style="text-align: center;;">I have the right amount of communication with L&D management and what we cover is relevant/useful to me</td><td style="text-align: center;;">My expectations are managed effectively and consistently</td><td style="text-align: center;;">I am challenged appropriately and positively when necessary</td><td style="text-align: center;;">L&D are proactive in their support of me and my business</td><td style="text-align: center;;">L&D provide useful and effective feedback and reflection </td><td style="text-align: center;;">L&D are credible learning and development professionals in my eyes</td><td style="text-align: center;;">L&D processes, templates, SLAs and rules of engagement work for me and support collaborative working</td><td style="text-align: center;;">Training resources are managed effectively</td><td style="text-align: center;;">L&D are responsive and solution-oriented, particularly under pressure</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="font-weight: bold;text-align: center;color: #C00000;;">6</td><td style="text-align: center;;">Stake Feedback</td><td style="text-align: center;;">DELIVERING OUTCOMES</td><td style="text-align: center;;">Regarding Design of learning journeys and materials, I am satisfied with what has been delivered</td><td style="text-align: center;;">I am satisfied with how my SMEs and managers have been involved in design and delivery of learning</td><td style="text-align: center;;">Classroom delivery has met my highest expectations</td><td style="text-align: center;;">Coaching and reinforcement activity as been appropriate and effective</td><td style="text-align: center;;">I am satisfied that the skills and capability that have been delivered through L&D programmes have met the agreed brief</td><td style="text-align: center;;">I am satisfied that learning activity in this period has delivered the behaviours and mindset we need to meet our objectives</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=COUNTA(<font color="Blue">E3:X3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=COUNTA(<font color="Blue">E4:X4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=COUNTA(<font color="Blue">E5:X5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=COUNTA(<font color="Blue">E9:X9</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">=COUNTA(<font color="Blue">E10:X10</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B11</th><td style="text-align:left">=COUNTA(<font color="Blue">E11:X11</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B15</th><td style="text-align:left">=COUNTA(<font color="Blue">E15:X15</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B19</th><td style="text-align:left">=COUNTA(<font color="Blue">E19:X19</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B20</th><td style="text-align:left">=COUNTA(<font color="Blue">E20:X20</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B21</th><td style="text-align:left">=COUNTA(<font color="Blue">E21:X21</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B25</th><td style="text-align:left">=COUNTA(<font color="Blue">E25:X25</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B26</th><td style="text-align:left">=COUNTA(<font color="Blue">E26:X26</font>)</td></tr></tbody></table></td></tr></table><br />

So when i look at the 1st sheet (this is determined by the TYPE name) - i need to look at each question for each section
I need to match the question for the 1st agent and retrieve all that data and repeat again (the section name will be the question im looking at for that section)

All i know is that column A will have every data filled in so the last row can be determined by column A and last column can be determined by row 2 which is the question row)
If what whatever reason the answer the question is anything other than a number (blank,?, NA etc) then return a 0

The Survey header range is named differently to the final output

Here is the range for how it appears in the rawdata (so i will need to look at that title header) in that sheet and return that value and paste in the relevant column on the output sheet

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Appears in Survey Monkey</td><td style="font-weight: bold;;">Final Output</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Respondent ID</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Respondent ID</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Your name:</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Start Date</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Please enter the Unique Reference Number of the training you attended (you can copy & paste this from the email you have received)</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">URN</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Please enter the programme name of the training you attended (you can copy & paste this from the email you have received)</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Programme Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Please select from the drop down menu the lead trainer for the session</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Lead Trainer Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Please enter your Area name</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Area</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Header</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Your base site:</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Base Site</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Results</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Other (please specify)</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">OTHER</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Results</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Der</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Derby</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Results</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Brum</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Bham</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Results</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Lon</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">London</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Results</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">N/A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Ref</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left"></td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">Appears in Survey Monkey</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">Final Output</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left"></td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left"></td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left"></td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">Respondent ID</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">Respondent ID</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A4</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">Your name:</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">Name</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">Start Date</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">Date</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A6</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">Please enter the Unique Reference Number of the training you attended (<font color="Blue">you can copy & paste this from the email you have received</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">URN</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A7</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">Please enter the programme name of the training you attended (<font color="Blue">you can copy & paste this from the email you have received</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C7</th><td style="text-align:left">Programme Name</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A8</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">Please select from the drop down menu the lead trainer for the session</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C8</th><td style="text-align:left">Lead Trainer Name</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A9</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">Please enter your Area name</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">Area</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A10</th><td style="text-align:left">Header</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">Your base site:</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C10</th><td style="text-align:left">Base Site</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A11</th><td style="text-align:left">Results</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B11</th><td style="text-align:left">Other (<font color="Blue">please specify</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C11</th><td style="text-align:left">OTHER</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A12</th><td style="text-align:left">Results</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">Der</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">Derby</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A13</th><td style="text-align:left">Results</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B13</th><td style="text-align:left">Brum</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C13</th><td style="text-align:left">Bham</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A14</th><td style="text-align:left">Results</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B14</th><td style="text-align:left">Lon</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C14</th><td style="text-align:left">London</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A15</th><td style="text-align:left">Results</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B15</th><td style="text-align:left">N/A</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C15</th><td style="text-align:left">0</td></tr></tbody></table></td></tr></table><br />

If the base site says Other (please specify) then look at the cell offset 1 to the right and see if it has any value in there (if it is blank or has N/A) then display OTHER
other than that look at the table and find the base site specified and change to the actual name found in the table specified

I really hope this is enough information

My aim is to just transpose all the data split by question but the main problem is that the questions are not in the same order and nor is the title - once that sheet is done - look at the next sheet and repeat process (when looking at the next sheet, the question range will change) again determined by the type name - that would be the next lookuprange

Thank You
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi all - i know the previous message looks messy so please ignore it if it dont make sense - i have had a go at writing some code and so far ive got it to work ok to input all title/header rows

Output of question column is Column 11

This is where i need to look at the sheet im looping through and then get which question section i need to look at

I have created a named range that has the sheet name and then the named range of the range it needs to look at ITS CALLED (QuestionLookup)

eg - if im on sheet Learner 1 (<2hrs) - on the next column i have have put the named range of L1_Less2hrs (Ive created a named range call this)

Here is the lookup range

Type (Sheet Name)Named Range
Learner 1 (<2hrs)L1_Less2hrs
Learner 1 (>2hrs)L1_More2hrs
Learner 2Learner2
TL FeedbackTL_Feedback
Stake FeedbackStake_Feedback

<colgroup><col><col></colgroup><tbody>
</tbody>

This named range has multiple rows that lists each section and then a list of questions for each section (In column B) i have put a count of how many questions i have for each section

Before i move on to the next agent - i need to loop through each question - get the rating and in put into the rating column (Output sheet column 12), i need input the question im looping through and put that in outputsheet column (11) and put the section name (in output sheet column 5)

the question lookup looks like this (L1_less2hrs lookup range)

# OF QUESTIONSTYPESECTIONQ1Q2
6Learner 1 (<2hrs)INVITATIONI received an invitation with sufficient noticeI was clear about the purpose of the training
10Learner 1 (<2hrs)TRAINING EVENTI was fully engaged throughout the training eventI had a clear view of the learning agenda & daily priorities
6Learner 1 (<2hrs)THE LEARNING ENVIRONMENT & RESOURCESThe training space worked for meWe had all the equipment we needed and it always worked

<tbody>
</tbody>

So the main thing im trying to do is just purely Seperate the questions by row - get the rating and input the section
the question lookup range depends on what sheet im looping through

hope this makes sense as im nearly there but dont know how to get to the final line

Thank You

Here is the VBA code ive tried to put together

Code:
Sub transposeme()


Dim RawDatash As Worksheet
Dim Outputsh As Worksheet
Dim RefSh As Worksheet
Dim QuestionSh As Worksheet
Dim ws As Worksheet


Dim i As Long
Dim Startrow As Long
Dim Lrow As Long
Dim Lcol As Long
Dim RawDataCol As Long
Dim OutputLR As Long
Dim MatchHeaders As Long


Dim myType As String
Dim BaseSite As String


Set RawDatash = Worksheets("Learner 1(<2hrs)")
Set Outputsh = Worksheets("Output")
Set RefSh = Worksheets("Ref")
Set QuestionSh = Worksheets("Q Sheet")


'start row of data output from raw data
Startrow = 3


Lrow = RawDatash.Range("A" & Rows.Count).End(xlUp).Row
Lcol = RawDatash.Cells(2, Columns.Count).End(xlToLeft).Column
OutputLR = Outputsh.Range("A" & Rows.Count).End(xlUp).Row
        
        
    For Each ws In ThisWorkbook.Worksheets
            'skip these sheets
        If ws.Name <> "Ref" Or ws.Name <> "Output" Or ws.Name <> "Q Sheet" Then
            
            'Loop through each agent
        For i = Startrow To Lrow
            myType = ws.Name
            
            'Loop through headers
            
                'Respondant
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(1), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 1).Value = RawDatash.Cells(i, MatchHeaders)
                
                'Name
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(2), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 2).Value = RawDatash.Cells(i, MatchHeaders)
                
                
                'Date
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(3), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 3).Value = DateValue(RawDatash.Cells(i, MatchHeaders))
                
                
                'URN
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(4), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 6).Value = RawDatash.Cells(i, MatchHeaders)
                
                'Area
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(6), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 7).Value = RawDatash.Cells(i, MatchHeaders)
                
    
                'Programme Name
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(5), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 8).Value = RawDatash.Cells(i, MatchHeaders)
                
                
                'Lead Trainer
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(6), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 9).Value = RawDatash.Cells(i, MatchHeaders)
                  
                'Base Site
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(8), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
   
                    Select Case RawDatash.Cells(i, MatchHeaders)
                        Case Is = "Other (please specify)"
                                BaseSite = "OTHER"
                        Case Is = "Lon"
                                BaseSite = "London"
                        Case Is = "Der"
                                BaseSite = "Derby"
                        Case Else
                                BaseSite = "OTHER"
                    End Select
                    Outputsh.Cells(OutputLR, 10).Value = BaseSite
                
                'Type
                Outputsh.Cells(OutputLR, 4).Value = myType
            
            '''#####STUCK HERE####'''''
            
            'Section Name
            'Outputsh.Cells(OutputLR, 5).Value
            
            'Question
            'Outputsh.Cells(OutputLR, 11).Value
            
            'Rating
            'Outputsh.Cells(OutputLR, 12).Value
            
            OutputLR = OutputLR + 1
            
            'Loop through each question and seperate them on its own lineand then move onto next agent and repeat
            'once done go to next sheet and follow same steps
            'Question range depends on which sheet im on
            ''''#################'''''''
            
            
        Next i
       End If
    Next ws
End Sub
 
Last edited:

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi - I managed to input and look at the correct named range and loop over all questions for that question section - the bit that it fails currently is that once it loops through one question - i need to go to the next row on the output sheet to look at the next question but not the next agent as ill need to loop through all the questions 1st - all the rows for that agent should be the same except the questions and rating will be different

If there is no match - i currently have on error resume next but i guess there will be a better way to do it

Please - i know post 1 is confusing and i wish i could delete it but hopefully just need tweaking this code and if this code can be mademore efficient - would really appreciate if you could help me with that

thank you
Code:
Sub transposeme()


Dim RawDatash As Worksheet
Dim Outputsh As Worksheet
Dim RefSh As Worksheet
Dim QuestionSh As Worksheet
Dim ws As Worksheet
Dim QuestionRange As Range


Dim i As Long
Dim Startrow As Long
Dim Lrow As Long
Dim Lcol As Long
Dim RawDataCol As Long
Dim OutputLR As Long
Dim MatchHeaders As Long


Dim myType As String
Dim BaseSite As String


Set RawDatash = Worksheets("Learner 1(<2hrs)")
Set Outputsh = Worksheets("Output")
Set RefSh = Worksheets("Ref")
Set QuestionSh = Worksheets("Q Sheet")


'start row of data output from raw data
Startrow = 3


Lrow = RawDatash.Range("A" & Rows.Count).End(xlUp).Row
Lcol = RawDatash.Cells(2, Columns.Count).End(xlToLeft).Column
OutputLR = Outputsh.Range("A" & Rows.Count).End(xlUp).Row
        
        
    For Each ws In ThisWorkbook.Worksheets
            'skip these sheets
        If ws.Name <> "Ref" Or ws.Name <> "Output" Or ws.Name <> "Q Sheet" Then
            
            'Loop through each agent
        For i = Startrow To Lrow
            myType = ws.Name
            
            'Loop through headers
            
                'Respondant
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(1), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 1).Value = RawDatash.Cells(i, MatchHeaders)
                
                'Name
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(2), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 2).Value = RawDatash.Cells(i, MatchHeaders)
                
                
                'Date
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(3), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 3).Value = DateValue(RawDatash.Cells(i, MatchHeaders))
                
                
                'URN
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(4), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 6).Value = RawDatash.Cells(i, MatchHeaders)
                
                'Area
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(6), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 7).Value = RawDatash.Cells(i, MatchHeaders)
                
    
                'Programme Name
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(5), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 8).Value = RawDatash.Cells(i, MatchHeaders)
                
                
                'Lead Trainer
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(6), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
                Outputsh.Cells(OutputLR, 9).Value = RawDatash.Cells(i, MatchHeaders)
                  
                'Base Site
                MatchHeaders = Application.WorksheetFunction.Match(RefSh.ListObjects("HeaderTable").ListColumns("Appears in Survey Monkey").DataBodyRange(8), RawDatash.Range(RawDatash.Cells(1, 1), RawDatash.Cells(1, Lcol)), 0)
   
                    Select Case RawDatash.Cells(i, MatchHeaders)
                        Case Is = "Other (please specify)"
                                BaseSite = "OTHER"
                        Case Is = "Lon"
                                BaseSite = "London"
                        Case Is = "Der"
                                BaseSite = "Derby"
                        Case Else
                                BaseSite = "OTHER"
                    End Select
                    Outputsh.Cells(OutputLR, 10).Value = BaseSite
                
                'Type
                Outputsh.Cells(OutputLR, 4).Value = myType
            
            '''#####STUCK HERE####'''''
            
            'Get Question Named Range
            QuestionNamedRange = Application.WorksheetFunction.VLookup(myType, QuestionSh.Range("QuestionLookup"), 2, False)
            Set QuestionRange = QuestionSh.Range(QuestionNamedRange)
    
            For rr = 1 To QuestionRange.Rows.Count


                'Section Name
                Outputsh.Cells(OutputLR, 5).Value = QuestionRange.Cells(rr, 1).Offset(, 1).Value
                
                For cc = 3 To QuestionRange.Cells(rr, 1).Offset(, -1) + 2
                    'Loop through questions
                    'Question Name
                    Outputsh.Cells(OutputLR, 11).Value = QuestionRange.Cells(rr, cc).Value
                        
                    'Rating
                    On Error Resume Next
                         MatchHeaders = Application.WorksheetFunction.Match(QuestionRange.Cells(rr, cc).Value, RawDatash.Range(RawDatash.Cells(2, 1), RawDatash.Cells(2, Lcol)), 0)
                        Outputsh.Cells(OutputLR, 12).Value = RawDatash.Cells(i, MatchHeaders)
                    On Error GoTo 0
                Next cc
            Next rr
            
            OutputLR = OutputLR + 1
            
            'Loop through each question and seperate them on its own lineand then move onto next agent and repeat
            'once done go to next sheet and follow same steps
            'Question range depends on which sheet im on
            ''''#################'''''''
        Next i
       End If
    Next ws
End Sub
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Any help is appreciated- thank you so much
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,308
Members
410,545
Latest member
Upsindustrial20
Top