VBA Excel Consolidate survey data
This tutorial explains how to use VBA Excel to consolidate survey data. It is very difficult to describe what I meant, 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
VBA Excel Consolidate survey data
