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!

 

[eagle_eye]

Hello Caderas de Serpiente,

While trying to conceive is quite enjoyable, it is also rather exhausting so I was just wondering if you've had any time to consider a possible solution to my Excel problem?

 

[Snakehips]

Ola Ojo de aguila,

On the 6th Nov I sent you a PM with a view to trying to help and as yet, I have had no response from you.
Given that in the meantime you have had responses from Gwinn and par60056 I assumed that you had perhaps sorted things.

Little did I know that all this time, having put your Excel to one side, you've been 'at it like a rabbit'.
Tell me, are you fortunate to be with a partner who like 'baldies' or has the hair not fallen out yet?

When you get your strength back, check your PMs and PM me back.

Caderas

 

[eagle_eye]

Dios Mio!

I have been panting away for nothing! I don't get notifications for PMs (not PMS, I do get notification of that!) so I had no idea I had such a gift waiting for me! I shall check right now!

 

[Snakehips]

eagle_eye,

I have a solution pending.
Please contact before I post it.

 

[Snakehips]

eagle_eye et al,

For completeness of the thread and having had the benefit of seeing eagle_eye's file, here is the coded solution.

It requests required user data input then displays a file dialog to allow browsing to the CSV file.
Imports the CSV, converts text responses to numeric scores and transfers all data to a data sheet.
There are 120 sets of classroom data on data sheet. The position of each set in the data sheet is controlled by an offset related to it's classroom number.

Strictly, not the subject of this post, but changing most formulas in eagle_eyes Classroom and Summary sheets to use the OFFSET () function to retreive data based on the classroom number meant that 120 classroom sheets could be reduced to 1.

Sub ImportCSV()
'******Imports selected csv file to CSV sheet then puts data to data sheet

'NB  ** Any existing data for selected classroom will be overwritten.

'Ensure not in maintain mode
If Sheets("Evaluation Summary").Shapes("Rounded Rectangle 4").TextFrame.Characters.Text = "Quit Maintain" Then
Ans = MsgBox("Please click 'Quit Maintain' then try again.", vbOKOnly, "Whoops!")
Exit Sub
End If
Application.ScreenUpdating = False
'Set object variable for sheets
Set WsC = Sheets("CSV")
Set WsD = Sheets("DATA")
'Get Classroom number from user
CRAgain:
CRoom = InputBox("Please enter the classroom NUMBER", "Classroom Number")
'****************
'Check a valid classroom ie 1 to 120
If Not (CRoom > 0 And CRoom < 121) Then
                                        
'*****************
Ans = MsgBox("Please enter a number between 1 and 120 " & Chr(13) & Chr(13) & " Or Cancel to quit", vbOKCancel, "Need Valid Data")
If Ans = vbCancel Then Exit Sub
GoTo CRAgain
End If
'Get Date from user
DateAgain:
ClassDate = InputBox("Please enter the class DATE", "Class Date")
If ClassDate = "" Then
Ans = MsgBox("Please enter a valid date" & Chr(13) & Chr(13) & " Or Cancel to quit", vbOKCancel, "Need Valid Data")
If Ans = vbCancel Then Exit Sub
GoTo DateAgain
End If
'Get Session from user
SessionAgain:
Session = InputBox("Please enter the class SESSION", "Session")
If Session = "" Then
Ans = MsgBox("Please enter a valid session" & Chr(13) & Chr(13) & " Or Cancel to quit", vbOKCancel, "Need Valid Data")
If Ans = vbCancel Then Exit Sub
GoTo SessionAgain
End If
'Set the classroom's first row in data sheet
DataRow = 1 + ((CRoom - 1) * 15)
'Clear the current CSV sheet
WsC.UsedRange.ClearContents
'Select and import the csv
 '************
  'Create a File Dialog as a file picker to select a csv file
Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = "Browse to the CSV File"
        .AllowMultiSelect = False
        .ButtonName = "Select CSV File"
        
        'Path to folder containing csv files
        '***********
        .InitialFileName = "E:\Eagle Eye"  '***** Test Path
        
    
        '***********
        'filter for csv files only
        .Filters.Add "CSV Files", "*.csv", 1
'Show dialog and jump out if user presses cancel
        If .Show <> -1 Then Exit Sub
        'get filename
        MyCSVName = .SelectedItems(1)
    End With
    
    'Import csv file to CSV sheet
    With WsC.QueryTables.Add(Connection:= _
        "TEXT;" & MyCSVName, Destination:=WsC.Range("$A$1"))
        .Name = "CSV"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 2        ' set at 2 omits header row which is prone to tab and separator inconsistencies
                                    'Fortunately headers not required
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 With WsC
'convert text responses to scores
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For MyCol = 11 To 20
        For MyRow = 2 To LastRow
        
        Select Case .Cells(MyRow, MyCol)
            Case "Strongly Agree"
                Score = 5
            Case "Agree"
                Score = 4
            Case "Neutral"
                Score = 3
            Case "Disagree"
                Score = 2
            Case "Strongly Disagree"
                Score = 1
            Case Else
                If Not IsNumeric(.Cells(MyRow, MyCol).Value) Then Score = ""
                
        End Select
        
        .Cells(MyRow, MyCol).Value = Score
            
        Next MyRow
    Next MyCol
    
    
End With

'Put other details into DATA sheet
With WsD.Cells(DataRow, 1)
.Offset(1, 0).Value = WsC.Range("J2") 'Trainer name
.Offset(1, 1).Value = Session 'Session
.Offset(1, 2).Value = ClassDate  'ClassDate
.Offset(1, 3).Value = LastRow - 1  'Participants = # of responds
End With
  'clear classroom's data from Data
 WsD.Range(WsD.Cells(DataRow + 3, 2), WsD.Cells(DataRow + 14, 101)).ClearContents
 
 
 'transpose CSV scores to Data
WsD.Range(WsD.Cells(DataRow + 3, 2), WsD.Cells(DataRow + 12, LastRow)).Value = _
Application.Transpose(WsC.Range(WsC.Cells(2, 11), WsC.Cells(LastRow, 20)))

'Put Comments to DATA sheet
ComRow = DataRow + 14
ComCol = 2
For Counter = 2 To LastRow
If Not WsC.Cells(Counter, 21).Value = "" Then
WsD.Cells(ComRow, ComCol).Value = WsC.Cells(Counter, 21).Value
ComCol = ComCol + 1
End If
Next Counter
'Show result
Sheets("Classroom").Range("O2").Value = CRoom
Sheets("Classroom").Select

Application.ScreenUpdating = True
End Sub

 

[eagle_eye]

Snakehips, this works PERFECTLY!!! I understand practically nothing you have posted above but it works!! My hair has been saved!

I have searched the dictionary, at length, to find a superlative to describe just how glorious you are but was unable to find one. Therefore, I would like to recommend that a statue be erected in your honor right in the middle of downtown. We don't have any more room here in our downtown but if someone could forward me the name of a small village that probably hasn't had any heroes that require a statue, I will contact them straight away to petition a statue for Snakehips.

In the meantime, I will work diligently on that first born so that you will have a namesake! Not quite as public as a statue but I'm sure he will make you proud!

A thousand 'thank yous' and then some!!!!