IF this doesn't get figured out soon, THEN all of my hair will turn white and fall out onto the floor!!

[eagle_eye]

I apologize for the dramatics but I am, once again, at my wits end. I have a task that I have been spinning around in circles trying to figure out for several weeks and I've concluded that the issue is one of three things:

1. I'm approaching it from all of the wrong angles
2. I'm making it more complicated than it needs to be
3. The task is simply impossible

I'm hoping for #1 or #2. On to the issue.

I have a CSV file from a survey site that serves as the raw data for a template for training sessions. I also have a template that we have been manually transferring the raw data into. I'm looking for a way to automate that process and it's proving to be a lot more challenging than I'd originally thought.

Here is a sample of the CSV file:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	RespondentID	StartDate	EndDate	Trainer Name:	The course content was of excellent quality. - Response:	I acquired new knowledge and skills. - Response:	I will apply the concepts learned today in my day-to-day activities. - Response:	I found the hands-on exercises relevant and a good preparation for what I will do in my job. - Response;	The reference materials will be a beneficial resource after training. - Response:	The trainer was knowledgeable with the training content/concepts. - Response:	The trainers presentation style was enthusiastic, motivating, and energetic. - Response:	The training methods used were effective. - Response:	The trainer conducted the exercises in a clear and efficient manner. - Response	I found this course beneficial and would recommend it to others. - Response:	Comments: - Open-Ended Response
2	2269916020	10/18/2012	10/18/2012	Trainer 1	4	4	4	4	4	4	4	4	4	4
3	2267471205	10/17/2012	10/17/2012	Trainer 1	1	1	1	1	1	1	1	1	1	1
4	2267470197	10/17/2012	10/17/2012	Trainer 1	4	4	5	4	5	4	3	4	4	3
5	2267469760	10/17/2012	10/17/2012	Trainer 1	5	5	5	5	5	5	5	5	5	5
6	2267469668	10/17/2012	10/17/2012	Trainer 1	5	5	5	5	5	5	5	5	5	5
7	2267469116	10/17/2012	10/17/2012	Trainer 1	5	4	4	4	4	4	4	4	5	5
8	2267468863	10/17/2012	10/17/2012	Trainer 1	4	5	4	5	4	5	5	4	4	5
9	2267468655	10/17/2012	10/17/2012	Trainer 1	5	5	5	5	3
10	2267468201	10/17/2012	10/17/2012	Trainer 1	4	4	4	4	4	4	4	4	4	4
11	2267468094	10/17/2012	10/17/2012	Trainer 1	5	5	5	5	5	5	5	5	5	5
12	2267467767	10/17/2012	10/17/2012	Trainer 1	4	4	5	5	4	4	4	4	4	4
13	2267467723	10/17/2012	10/17/2012	Trainer 1	4	4	4	5	5	4	4	4	4	4
14	2267467643	10/17/2012	10/17/2012	Trainer 1	4	4	4	4	4	5	4	4	4	4
15	2267467459	10/17/2012	10/17/2012	Trainer 1	5	5	5	5	5	5	5	5	5	5
16	2267443312	10/17/2012	10/17/2012	Trainer 1	5	5	5	5	5	5	5	5	5	5	I still prefer live training, however, this is a good second choice. My computer lost audio and I phoned in - this was confusing for several minutes but turned out ok. Thank you!
17	2267438669	10/17/2012	10/17/2012	Trainer 1	4	4	4	4	4	4	4	4	4	3
Test Sheet

The trainer name and the scores for each question need to go into this data sheet:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Classroom 1
2	Trainer Name
3		P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	P15
4	Q1	5	5	5	5	5	5	5	5	4	4	4	4	4
5	Q2	5	5	5	5	5	5	5	4	4	4	4	4	4
6	Q3	5	5	5	5	5	5	5	5	4	4	4	4	3
7	Q4	5	5	5	5	5	5	5	5	5	4	4	4	4
8	Q5	5	5	5	5	5	5	5	5	5	4	4	4	4
9	Q6	5	5	5	5	5	5	5	5	4	4	4	4	4
10	Q7	5	5	5	5	5	5	5	4	4	4	4	4
11	Q8	5	5	5	5	5	5	5	5	4	4	4	4
12	Q9	5	5	5	5	5	5	5	5	4	4	4	2
13	Q10
14	Q11
DATA

Additionally, the trainer name and date from the CSV need to go at the top of this spreadsheet and any comments from the CSV need to go at the bottom:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Classroom	Trainer	Date	# of Participants	Class/Session		Overall Training Content Score	Overall Presentation Score
2	1	Trainer Name	9/26/12	13	3:30 PM		4.60	4.61
3
4	Individual Scores
5
6	Course Material					Workshop Presentation
7	Question		Score	% Answered		Question		Score	% Answered
8	1	The course content was of excellent quality.	4.62	100%		5	The trainer was knowledgeable with the training content/concepts.	4.69	100%
9	2	I acquired new knowledge and skills.	4.54	100%		6	The trainer's presentation style was enthusiastic, motivating and energetic.	4.62	100%
10	3	I will apply the concepts learned today in my day-to-day activities.	4.54	100%		7	The training methods used were effective.	4.58	92%
11	4	I found the demos/exercises relevant and a good preparation for what I will do in my job.	4.69	100%		8	The trainer conducted the exercises in a clear and efficient manner.	4.67	92%
12						9	I found this course beneficial and would recommend it to others.	4.50	92%
13
14	Classroom Comments
15	Due to the delay/technical issues, we needed more time to go over concepts, etc., in detail to retain knowledge or go over again in next meeting session.
16	Great work, Trainer!
17	Great class.
18
19
Classroom 1

Cell Formulas
Range	Formula
B2	=DATA!A2
G2	=AVERAGE(C8:C11)
H2	=AVERAGE(H8:H12)
H8	=DATA!CZ8
H9	=DATA!CZ9
H10	=DATA!CZ10
H11	=DATA!CZ11
H12	=DATA!CZ12
C8	=DATA!CZ4
C9	=DATA!CZ5
C10	=DATA!CZ6
C11	=DATA!CZ7
D8	=DATA!CY4/'Classroom 1'!$D$2
D9	=DATA!CY5/'Classroom 1'!$D$2
D10	=DATA!CY6/'Classroom 1'!$D$2
D11	=DATA!CY7/'Classroom 1'!$D$2
I8	=DATA!CY8/'Classroom 1'!$D$2
I9	=DATA!CY9/'Classroom 1'!$D$2
I10	=DATA!CY10/'Classroom 1'!$D$2
I11	=DATA!CY11/'Classroom 1'!$D$2
I12	=DATA!CY12/'Classroom 1'!$D$2

I have tried various VLOOKUP combinations but I get myself so tangled up that I'm lucky to get one set of data to show up in the right place. Also, I don't know how to get it to continue running whatever function I create until all of the data is migrated. Did I mention that I am a complete newb when it comes to VBA? I have tried modifying macros that I've found across the web but I don't know enough to know if the macro didn't work or my modification was wrong. And, because my brain is trying to resolve everything at once, I can't even figure out what I should search for. It's like spelunking on message boards.

Anyway, if anyone has any idea of how I can approach this or even what kind of things I should search for to get me moving in the right direction, I will name my first child after you.

Thank you!

 

[Snakehips]

eagle_eye,

Good news / Bad news

The good news is that I have a way of sorting this out for you.

The bad news is that I think that it would be too cruel to saddle your first child with a name like Snakehips so on that basis I will have to withhold my solution.

I would recommend you Google 'Toupee's R Us"

 

[eagle_eye]

eagle_eye,

Good news / Bad news

The good news is that I have a way of sorting this out for you.

The bad news is that I think that it would be too cruel to saddle your first child with a name like Snakehips so on that basis I will have to withhold my solution.

I would recommend you Google 'Toupee's R Us"

You are correct. Snakehips might be a bit much but Caderas de Serpiente sounds a little more exotic so I could probably get away with it. Please bring on the good news and I will get about the business of trying to conceive my new little reptile.

 

[Snakehips]

eagle_eye,

Sorry about the last post but could not resist it. If nothing else it promoted your question back to page 1.
I do not have time available today however I will take a look at your problem tomorrow and see what I can offer.

Is the csv file exactly the same format each time?

Caderas de Serpiente sounds just perfect. Go to it with the conceiving.

 

[eagle_eye]

No need to apologize! At this point, any response is better than none and when it prompts the kind of laughter that yours did, it's well worth the reading!

The CSV file is the same format every time. I eliminated some blank columns that I presume will be blank every time. The responses actually return words (Agree, Strongly Agree, etc.) that I run a macro on to turn those to values. I shed real tears when I got that to work. It was all downhill from there. I thought about using one of the blank columns to have someone manually enter the Classroom number and the Class/Session information since that is not part of the output in the CSV file but I have no idea how to call it. What am I talking about? I have no idea how to do anything that will put that CSV data into the worksheet!

Anyway, I really appreciate any guidance you can provide. I can't promise that Caderas will be here in exactly nine months but I will do all that I can!

 

[Gwinn]

I will try and help you with the first part, which is getting the data from the CSV template into you data sheet.

1. I assume P1, P2 etc in the data sheet are the actual student IDs (e.g. in place of "P1" is "2269916020") and that Q1, Q2 etc are the actual questions (e.g. in place of "Q1" is "The course content was of excellent quality. - Response:"). You can use formulas to get those values, or they can be hard filled.
2. Copy =INDEX('raw data'!$E$2:$N$1048576;MATCH('output 1'!B$3;'raw data'!$A$2:$A$50;0);MATCH('output 1'!$A4;'raw data'!$E$1:$N$1;0)) into B4 on your data sheet and extend to cover all students and all question
3. ????
4. Profit

Hope it helps

 

[par60056]

Can you use a macro or does it have to be done with formulas?

Personally I see everything as an opportunity to use a pivot table. I build them via macros all the time.

This will give you a start. I assume that there is more than 1 trainer. After building the pivot you can cycle the the page fields of trainers, copy the result out of the pivot to a trainer score sheet and then calculate the average for each question. (and look the question text back up on the other sheet)

Function colText(ByVal colNum As Integer) As String
Dim ct As String
ct = ""
ct = Chr(((colNum - 1) Mod 26) + Asc("A"))
colNum = colNum - (((colNum - 1) Mod 26) + 1)
If colNum >= 26 Then
    ct = Chr((((colNum - 26) \ 26) Mod 26) + Asc("A")) & ct
    colNum = colNum - (((((colNum - 1) \ 26) Mod 26) + 1) * 26)
End If
If colNum >= 676 Then
    ct = Chr((((colNum - 676) \ 676) Mod 26) + Asc("A")) & ct
End If


colText = ct


End Function


Sub surveyReport()
Dim lastRow As Long
Dim lastCol As Integer
Dim currCol As Integer
Dim sourceSheet As String


sourceSheet = ActiveSheet.Name


lastCol = ActiveSheet.UsedRange.Columns.Count
lastRow = ActiveSheet.UsedRange.Rows.Count


' 1st copy the questions to another sheet.  Too much work to pivot the question text
Sheets.Add
ActiveSheet.Name = "Questions"
Sheets(sourceSheet).Activate
Range(Cells(1, 5), Cells(1, lastCol)).Copy
Sheets("Questions").Cells(1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True


' change all the question text to question numbers.  you can look them up later on the question sheet
For currCol = 5 To lastCol
    Cells(1, currCol) = "Q" & currCol - 4
Next




Sheets.Add
ActiveSheet.Name = "Responces"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "'" & sourceSheet & "'!A1:" & colText(lastCol) & lastRow, Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:=Range("Responces!A1"), TableName:="responceTable", DefaultVersion _
    :=xlPivotTableVersion12
With ActiveSheet.PivotTables("responceTable")
    .HasAutoFormat = False
    .PreserveFormatting = False
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    With .PivotFields("Trainer Name:")
        .Orientation = xlPageField
        .Position = 1
    End With
    With .PivotFields("RespondentID")
        .Orientation = xlColumnField
        .Position = 1
    End With
    For currCol = 5 To lastCol
        .AddDataField .PivotFields("Q" & currCol - 4), "tQ" & currCol - 4, xlSum
        With .PivotFields("tQ" & currCol - 4)
            .Orientation = xlDataField
        End With
    Next
    With .DataPivotField
        .Orientation = xlRowField
        .Position = 1
    End With
    Application.ScreenUpdating = True
    
    .ColumnGrand = False
    .RowGrand = False
End With


End Sub

Hope this helps.

 

[par60056]

I already see a few things I would do differently. You also need the date as a page field so that you can look at different dates for a trainer. I would name the Question titles differently so that you can name the data rows properly. Maybe make the "QU#" instead of "Q#" and the name the pivot field "Q#" instead of "tQ#"

 

[eagle_eye]

Thanks for responding, Gwinn! I will need a little clarification on your response. I'm not sure what you mean in #1 where you say "You can use formulas to get those values, or they can be hard filled.". "P1" and "Q1" don't need to be pulled from the CSV file. Only the values that correspond to those participants and questions. Your formula in #2 references both an "output 1" and a "raw data" sheet. Which of my sheets get plugged into those?

And, I'm sorry, but I have no idea what you mean by #3 and #4.

Thank you also, par60056! I'm very familiar with pivot tables so I feel like I should be able to follow you but I was lost right away. What data would the pivot table be built upon? The CSV files are outputs from an online survey site so you are correct that there will be several trainers but each one will have a unique CSV file. Ideally, a macro could be run on each CSV file that would take the Trainer Name from the CSV file and place it in A2 of the Data sheet. Then, it would look again at the CSV file and take the value from E2 of the CSV file and place it in B4. Go back and take the next value from F2 and put it in B5 and so on. It would continue to grab those values until all of the scores for each student have been filled in to the Data sheet. Then, it would go back again to the CSV file and see if any comments were in Column L. If so, it would copy and paste those comments on the Classroom 1 spreadsheet in A15, A16 and so on. The actual "P"s and "Q"s don't need to change at all. If I get that far, the only thing left is getting the right trainer in the right classroom because there is a summary sheet that has hyperlinks to the classrooms and in order for them to show up in chronological order, they have to be in that order on the Data sheet.

Of course, that is my Utopia.

 

[par60056]

I assumed that the first table you had was the CSV file that came from the survey system. (It looks like ones I have seen from other systems) I am surprised that the survey system gives you 1 CSV for each trainer. Since trainer name was a column in the file I assumed there could be multiple trainers and multiple dates in 1 CSV.

I'm sure that what you want can be done. It can probably be done several ways. Sometimes getting started is the hard part and once you get a little start you will be able to figure out the rest.