Complicated transpose of data with conditions

mahmed1 · May 19, 2018

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

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

2018-05-17 16:33:43

2018-05-17 16:35:43

test

102-TR-May-18-7-TM1

TM1

PROGRAMME1

Name1

Name 1

Other (please specify)

Lake House

4

TEST

4

TEST

3

4

TEST

4

test

2018-05-17 16:09:18

2018-05-17 16:23:53

test

102-TR-May-18-7-TM1

TM1

PROGRAMME1

Name1

Name 2

Derby

5

4

TEST

5

TEST

2

5

TEST

5

test

2018-05-17 14:55:22

2018-05-17 14:57:15

test

102-TR-May-18-7-TM1

TM1

PROGRAMME1

Name1

Name 3

London

4

5

4

...................

5

2

1

5

3

TEST

3

4

5

TEST

6

test

2018-05-17 14:23:45

2018-05-17 14:25:39

test

102-TR-May-18-7-TM1

TM1

PROGRAMME1

Name1

Name 4

London

5

4

NA

1

2

3

4

5

N/A

TEST

3

TEST

7

test

2018-05-17 14:01:01

2018-05-17 14:02:54

test

102-TR-May-18-7-TM1

TM1

PROGRAMME2

Name1

Name 5

Other (please specify)

na

3

4

2

4

NA

4

TEST

4

3

5

TEST

8

test

2018-05-17 13:46:26

2018-05-17 13:48:17

test

102-TR-May-18-7-TM1

TM1

PROGRAMME2

Name1

Name 6

Manchester

5

4

NA

5

4

TEST

5

TEST

9

test

2018-05-17 13:10:12

2018-05-17 13:12:02

test

102-TR-May-18-7-TM1

TM1

PROGRAMME2

Name1

Name 7

Manchester

4

NA

5

4

5

4

TEST

4

3

TEST

10

test

2018-05-17 13:07:45

2018-05-17 13:11:25

test

102-TR-May-18-7-TM1

TM1

PROGRAMME2

Name1

Name 8

Derby

1

4

NA

5

N/A

1

3

1

NA

11

test

2018-05-17 13:05:46

2018-05-17 13:09:22

test

102-TR-May-18-7-TM1

TM1

PROGRAMME2

Name1

Name 9

Manchester

5

4

NA

N/A

NA

12

test

2018-05-14 14:31:51

2018-05-14 14:53:40

test

95-TR-May-18-5-TM2

TM2

PROGRAMME3

Name1

Name 10

Manchester

4

2

5

4

NA

5

4

5

4

5

N/A

3

2

5

NA

13

test

2018-05-11 15:08:44

2018-05-11 15:15:42

test

95-TR-May-18-5-TM2

TM2

PROGRAMME3

Name1

Name 11

London

5

4

NA

5

N/A

3

5

N/A

5

3

NA

14

test

2018-05-11 15:06:21

2018-05-11 15:08:36

test

95-TR-May-18-5-TM2

TM2

PROGRAMME3

Name 2

Name 12

Manchester

3

4

5

4

NA

3

N/A

3

4

NA

15

test

2018-05-11 13:53:40

2018-05-11 13:56:30

test

95-TR-May-18-5-TM2

TM2

PROGRAMME3

Name 3

Name 13

London

4

3

5

4

NA

4

5

3

4

5

1

N/A

4

1

3

NA

16

test

2018-05-11 13:53:38

2018-05-11 13:57:32

test

95-TR-May-18-5-TM2

TM2

PROGRAMME3

Name 1

Name 14

London

5

4

5

4

NA

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

2

I received an invitation with sufficient notice

3

I was clear about the purpose of the training

I was clear about venues, dates and timings

5

The invitation was inviting and engaging

6

The preparation made sense given the purpose

7

I understood why I had been invited

8

I was fully engaged throughout the training event

9

I felt able to ask questions and ask for help

10

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

4

5

6

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

10

11

Other (please specify)

12

13

14

15

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 · May 19, 2018

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 2	Learner2
TL Feedback	TL_Feedback
Stake Feedback	Stake_Feedback

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 QUESTIONS	TYPE	SECTION	Q1	Q2
6	Learner 1 (<2hrs)	INVITATION	I received an invitation with sufficient notice	I was clear about the purpose of the training
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
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

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

mahmed1 · May 20, 2018

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 · May 20, 2018

Any help is appreciated- thank you so much

Complicated transpose of data with conditions

mahmed1

Well-known Member

Excel Facts

mahmed1

Well-known Member

mahmed1

Well-known Member

mahmed1

Well-known Member

Similar threads

Forum statistics

Share this page

Complicated transpose of data with conditions

mahmed1

Well-known Member

Excel Facts

mahmed1

Well-known Member

mahmed1

Well-known Member

mahmed1

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock