Complicated transpose of data with conditions

[mahmed1]

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

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

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

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Respondent ID	Name	Date	Type	Section	URN	Area	Programme Name	Lead Trainer Name	Base Site	Question	Rating
2	test	Name 1	17/05/2018	Learner 1(<2hrs)	INVITATION	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	I received an invitation with sufficient notice	4
3	test	Name 1	17/05/2018	Learner 1(<2hrs)	INVITATION	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	I was clear about the purpose of the training	4
4	test	Name 1	17/05/2018	Learner 1(<2hrs)	INVITATION	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	I was clear about venues, dates and timings	4
5	test	Name 1	17/05/2018	Learner 1(<2hrs)	INVITATION	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	The invitation was inviting and engaging	4
6	test	Name 1	17/05/2018	Learner 1(<2hrs)	INVITATION	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	The preparation made sense given the purpose	4
7	test	Name 1	17/05/2018	Learner 1(<2hrs)	INVITATION	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	I understood why I had been invited	4
8	test	Name 1	17/05/2018	Learner 1(<2hrs)	TRAINING EVENT	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	I was fully engaged throughout the training event	4
9	test	Name 1	17/05/2018	Learner 1(<2hrs)	TRAINING EVENT	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	I felt able to ask questions and ask for help	4
10	test	Name 1	17/05/2018	Learner 1(<2hrs)	TRAINING EVENT	102-TR-May-18-7-TM1	TM1	PROGRAMME1	Name1	Lake House	The practical sessions helped bring the theory to life	4
Output

Cell Formulas
Range	Formula
A1	Respondent ID
A2	test
A3	test
A4	test
A5	test
A6	test
A7	test
A8	test
A9	test
A10	test
B1	Name
B2	Name 1
B3	Name 1
B4	Name 1
B5	Name 1
B6	Name 1
B7	Name 1
B8	Name 1
B9	Name 1
B10	Name 1
C1	Date
C2	43237
C3	43237
C4	43237
C5	43237
C6	43237
C7	43237
C8	43237
C9	43237
C10	43237
D1	Type
D2	Learner 1(<2hrs)
D3	Learner 1(<2hrs)
D4	Learner 1(<2hrs)
D5	Learner 1(<2hrs)
D6	Learner 1(<2hrs)
D7	Learner 1(<2hrs)
D8	Learner 1(<2hrs)
D9	Learner 1(<2hrs)
D10	Learner 1(<2hrs)
E1	Section
E2	INVITATION
E3	INVITATION
E4	INVITATION
E5	INVITATION
E6	INVITATION
E7	INVITATION
E8	TRAINING EVENT
E9	TRAINING EVENT
E10	TRAINING EVENT
F1	URN
F2	102-TR-May-18-7-TM1
F3	102-TR-May-18-7-TM1
F4	102-TR-May-18-7-TM1
F5	102-TR-May-18-7-TM1
F6	102-TR-May-18-7-TM1
F7	102-TR-May-18-7-TM1
F8	102-TR-May-18-7-TM1
F9	102-TR-May-18-7-TM1
F10	102-TR-May-18-7-TM1
G1	Area
G2	TM1
G3	TM1
G4	TM1
G5	TM1
G6	TM1
G7	TM1
G8	TM1
G9	TM1
G10	TM1
H1	Programme Name
H2	PROGRAMME1
H3	PROGRAMME1
H4	PROGRAMME1
H5	PROGRAMME1
H6	PROGRAMME1
H7	PROGRAMME1
H8	PROGRAMME1
H9	PROGRAMME1
H10	PROGRAMME1
I1	Lead Trainer Name
I2	Name1
I3	Name1
I4	Name1
I5	Name1
I6	Name1
I7	Name1
I8	Name1
I9	Name1
I10	Name1
J1	Base Site
J2	Lake House
J3	Lake House
J4	Lake House
J5	Lake House
J6	Lake House
J7	Lake House
J8	Lake House
J9	Lake House
J10	Lake House
K1	Question
K2	I received an invitation with sufficient notice
K3	I was clear about the purpose of the training
K4	I was clear about venues, dates and timings
K5	The invitation was inviting and engaging
K6	The preparation made sense given the purpose
K7	I understood why I had been invited
K8	I was fully engaged throughout the training event
K9	I felt able to ask questions and ask for help
K10	The practical sessions helped bring the theory to life
L1	Rating
L2	4
L3	4
L4	4
L5	4
L6	4
L7	4
L8	4
L9	4
L10	4

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

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	# OF QUESTIONS	TYPE	SECTION	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
3	6	Learner 1 (<2hrs)	INVITATION	I received an invitation with sufficient notice	I was clear about the purpose of the training	I was clear about venues, dates and timings	The invitation was inviting and engaging	The preparation made sense given the purpose	I understood why I had been invited
4	10	Learner 1 (<2hrs)	TRAINING EVENT	I was fully engaged throughout the training event	I had a clear view of the learning agenda & daily priorities	I felt able to ask questions and ask for help	The assessments helped me test my learning	The practical sessions helped bring the theory to life	The pace of the training worked for me	I am excited about putting my learning into practice	I am confident I will have the opportunity to put my learning into practice	I am clear about the support I can expect after the training event	I understand how this learning can help me be successful in my role
5	6	Learner 1 (<2hrs)	THE LEARNING ENVIRONMENT & RESOURCES	The training space worked for me	We had all the equipment we needed and it always worked	The slides, materials and handouts made learning easier	I am confident I will use the handouts and notes to help me when I'm 'live'	The group size worked well	The duration of the training was just right given what we were learning
6
7
8		TYPE	SECTION	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
9	3	Learner 1 (>2hrs)	INVITATION	I received an invitation with sufficient notice and all info I needed	The invitation was inviting and engaging	I understood why I had been invited
10	6	Learner 1 (>2hrs)	TRAINING EVENT	I was fully engaged throughout the training event	I felt able to ask questions and ask for help	The practical sessions helped bring the theory to life	The pace of the training worked for me	I am confident I can apply my learning	I am clear about the support I can expect after the training event
11	3	Learner 1 (>2hrs)	THE LEARNING ENVIRONMENT & RESOURCES	Training space, equipment and materials all worked for me	The duration of the training was just right given what we were learning	The group size worked for me
12
13
14		TYPE	SECTION	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
15	4	Learner 2	SINCE THE TRAINING	I have been able to put the learning into practice and my confidence is increasing as a result	I have had the support I need to successfully apply what I learned	The training has helped me perform better in my role	The training will help me perform better in my role in the future
16
17
18		TYPE	SECTION	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
19	3	TL Feedback	SET UP	I was clear about the purpose of the training	I had the right amount of contact and involvement with L&D in the run up	I was happy with the design of the training and what it would deliver
20	6	TL Feedback	TRAINING EVENT	I fully understood what was expected of me during the training	The training was well managed	The training was delivered in an engaging and interesting way (personal experience)	If N/A-didn't attend, branch to: The training was delivered in an engaging and interesting way (other's reports)	I am satisfied with the updates and communication I received throughout	I am satisfied with the capability of the Learners at this point
21	5	TL Feedback	REINFORCEMENT	I understood what I could expect from L&D in terms of supporting reinforcement	I understood what my role was in reinforcing the learning	I am satisfied with how L&D collaborated with me/us after the training	The reinforcement went as planned	I am satisfied that the reinforcement activity was successful
22
23
24		TYPE	SECTION	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10
25	9	Stake Feedback	RESOURCE & CLIENT MANAGEMENT	I have the right amount of communication with L&D management and what we cover is relevant/useful to me	My expectations are managed effectively and consistently	I am challenged appropriately and positively when necessary	L&D are proactive in their support of me and my business	L&D provide useful and effective feedback and reflection	L&D are credible learning and development professionals in my eyes	L&D processes, templates, SLAs and rules of engagement work for me and support collaborative working	Training resources are managed effectively	L&D are responsive and solution-oriented, particularly under pressure
26	6	Stake Feedback	DELIVERING OUTCOMES	Regarding Design of learning journeys and materials, I am satisfied with what has been delivered	I am satisfied with how my SMEs and managers have been involved in design and delivery of learning	Classroom delivery has met my highest expectations	Coaching and reinforcement activity as been appropriate and effective	I am satisfied that the skills and capability that have been delivered through L&D programmes have met the agreed brief	I am satisfied that learning activity in this period has delivered the behaviours and mindset we need to meet our objectives
Sheet3

Cell Formulas
Range	Formula
B3	=COUNTA(E3:X3)
B4	=COUNTA(E4:X4)
B5	=COUNTA(E5:X5)
B9	=COUNTA(E9:X9)
B10	=COUNTA(E10:X10)
B11	=COUNTA(E11:X11)
B15	=COUNTA(E15:X15)
B19	=COUNTA(E19:X19)
B20	=COUNTA(E20:X20)
B21	=COUNTA(E21:X21)
B25	=COUNTA(E25:X25)
B26	=COUNTA(E26:X26)

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

Book1
	A	B	C
1		Appears in Survey Monkey	Final Output
2
3	Header	Respondent ID	Respondent ID
4	Header	Your name:	Name
5	Header	Start Date	Date
6	Header	Please enter the Unique Reference Number of the training you attended (you can copy & paste this from the email you have received)	URN
7	Header	Please enter the programme name of the training you attended (you can copy & paste this from the email you have received)	Programme Name
8	Header	Please select from the drop down menu the lead trainer for the session	Lead Trainer Name
9	Header	Please enter your Area name	Area
10	Header	Your base site:	Base Site
11	Results	Other (please specify)	OTHER
12	Results	Der	Derby
13	Results	Brum	Bham
14	Results	Lon	London
15	Results	N/A	0
Ref

Cell Formulas
Range	Formula
B1	Appears in Survey Monkey
B3	Respondent ID
B4	Your name:
B5	Start Date
B6	Please enter the Unique Reference Number of the training you attended (you can copy & paste this from the email you have received)
B7	Please enter the programme name of the training you attended (you can copy & paste this from the email you have received)
B8	Please select from the drop down menu the lead trainer for the session
B9	Please enter your Area name
B10	Your base site:
B11	Other (please specify)
B12	Der
B13	Brum
B14	Lon
B15	N/A
C1	Final Output
C3	Respondent ID
C4	Name
C5	Date
C6	URN
C7	Programme Name
C8	Lead Trainer Name
C9	Area
C10	Base Site
C11	OTHER
C12	Derby
C13	Bham
C14	London
C15	0
A3	Header
A4	Header
A5	Header
A6	Header
A7	Header
A8	Header
A9	Header
A10	Header
A11	Results
A12	Results
A13	Results
A14	Results
A15	Results

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

 

[mahmed1]

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

[TABLE="width: 447"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Type (Sheet Name)[/TD]
[TD]Named Range[/TD]
[/TR]
[TR]
[TD]Learner 1 (<2hrs)[/TD]
[TD]L1_Less2hrs[/TD]
[/TR]
[TR]
[TD]Learner 1 (>2hrs)[/TD]
[TD]L1_More2hrs[/TD]
[/TR]
[TR]
[TD]Learner 2[/TD]
[TD]Learner2[/TD]
[/TR]
[TR]
[TD]TL Feedback[/TD]
[TD]TL_Feedback[/TD]
[/TR]
[TR]
[TD]Stake Feedback[/TD]
[TD]Stake_Feedback[/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="width: 1973"]
<tbody>[TR]
[TD]# OF QUESTIONS[/TD]
[TD]TYPE[/TD]
[TD]SECTION[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Learner 1 (<2hrs)[/TD]
[TD]INVITATION[/TD]
[TD]I received an invitation with sufficient notice[/TD]
[TD]I was clear about the purpose of the training[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Learner 1 (<2hrs)[/TD]
[TD]TRAINING EVENT[/TD]
[TD]I was fully engaged throughout the training event[/TD]
[TD]I had a clear view of the learning agenda & daily priorities[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Learner 1 (<2hrs)[/TD]
[TD]THE LEARNING ENVIRONMENT & RESOURCES[/TD]
[TD]The training space worked for me[/TD]
[TD]We had all the equipment we needed and it always worked[/TD]
[/TR]
</tbody>[/TABLE]

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

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

 

[mahmed1]

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

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]

Any help is appreciated- thank you so much