Hi all, first time poster, rather new to all things vba. I have a dataset that i need to clean up and i'm certain i can automate this task using VBA, but was hoping for your help on the second part.
Essentially i have a survey where students select the teacher they had, fill in a few questions using a 1-5 scale and then the final question is open ended responses. This means i end up with multiple entries for each teacher - my job is to consolidate the data such that i have one record of each teacher, with an average of each scaled response, and then a column for each of the open ended responses. The open ended responses are optional so there may only be one, but there may be up to ten. I begin by sorting my data based upon teacher name and then consolidating the table.
I am able to consolidate the raw data and scaled responses with the following vba code. Note this was just ran using record macro to get the general idea - the final data source would be contained within a table:
Sub Consolidate()
Application.CutCopyMode = False
Selection.Consolidate Sources:="[Book1]Sheet1!R1C1:R26C5", Function:= _
xlAverage, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub
However i cannot figure out how to transpose the open ended responses into subsequent columns. Any help would be appreciated. For reference, i have pasted an image of sample raw data i would receive and how i want it to look when finished:
Raw data:
End product:
Thanks!
Essentially i have a survey where students select the teacher they had, fill in a few questions using a 1-5 scale and then the final question is open ended responses. This means i end up with multiple entries for each teacher - my job is to consolidate the data such that i have one record of each teacher, with an average of each scaled response, and then a column for each of the open ended responses. The open ended responses are optional so there may only be one, but there may be up to ten. I begin by sorting my data based upon teacher name and then consolidating the table.
I am able to consolidate the raw data and scaled responses with the following vba code. Note this was just ran using record macro to get the general idea - the final data source would be contained within a table:
Sub Consolidate()
Application.CutCopyMode = False
Selection.Consolidate Sources:="[Book1]Sheet1!R1C1:R26C5", Function:= _
xlAverage, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub
However i cannot figure out how to transpose the open ended responses into subsequent columns. Any help would be appreciated. For reference, i have pasted an image of sample raw data i would receive and how i want it to look when finished:
Raw data:
End product:
Thanks!