VBA Excel Consolidate survey data

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