VBA Excel Consolidate survey data

This tutorial explains how to use VBA Excel to consolidate survey data.

Excel VBA Consolidate worksheets into one worksheet

Excel VBA consolidate multiple workbooks into one workbook

Excel VBA combine worksheets columns into one worksheet

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.

survey01

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.

survey02

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

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *