This tutorial explains how to use VBA Excel to consolidate survey data.
VBA Excel Consolidate survey data
It is very difficult to describe what I meant to consolidate survey data, take a look at the below picture.
The goal is to consolidate survey data as below. Normally, if you create online survey with Google, the report is generated as the below format.
This question was originally asked in Microsoft Forum and I provided a VBA solution. I have generalized and rewrote my solution below exclusively for this conversion.
VBA Sub Code – Excel Consolidate survey data
Public Sub consolSurvey() For c = 2 To Range("IV" & 1).End(xlToLeft).Column For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1 If Range("A" & r).Value = Range("A" & r - 1).Value And Cells(r, c).Value <> "" Then Cells(r - 1, c).Value = Cells(r, c).Value End If Next r Next c For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1 If Range("A" & r).Value = Range("A" & r - 1).Value Then Rows(r).EntireRow.Delete End If Next r End Sub
Algorithm of VBA Sub Code -Excel Consolidate survey data
The code is divided into two parts:
– The first part (in purple) checks the last column in row 1 and last row in column A as the looping end point
– Loop from the last row of data to first row of data of Column B, check if respondent name in column A is same as above, if TRUE move the data up to previous Cell
– After looping through column B for each row, loop other columns as well
– Finally in Part 2 (in red), if the respondent name is same as above, delete row
Assumption of VBA Sub Code – Excel Consolidate survey data
1) Survey table starts in A1, with column header in row 1 and data starting from row 2
2) It does not matter how many rows or columns in the table
2) The first column of table is the identifier (such as name) of respondent
3) The same identifier has to be put together in adjacent rows
4) For each person, only one answer for each question