Implement Advanced Data TransFormation Techniques with Excel VBA

Scenario

Let’s imagine you have a dataset with multiple columns, and you want to transform it into a more useful format. For example, you might need to:

1. Pivot a table of data (turn rows into columns).
2. Unpivot data (turn columns into rows).
3. Clean data by removing unwanted characters or handling missing values.
4. Apply complex filters or transform the data based on certain criteria.

I will break down the techniques and provide a VBA code example for each one.

1. Pivoting Data (Turning Rows into Columns)

Problem: You have a list of sales data for multiple sales representatives across different months, but the data is in rows, and you want to pivot it so that each month becomes a separate column.

Example Data:

Desired Output:

VBA Code for Pivoting Data:

Sub PivotData()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim dataRange As Range
    Dim pivotTable As PivotTable
    Dim pivotCache As PivotCache
    ' Set the worksheet and range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Create a Pivot Cache
    Set pivotCache = ThisWorkbook.PivotTableWizard(dataRange)
    ' Create the Pivot Table on a new sheet
    Set wsPivot = ThisWorkbook.Sheets.Add
    Set pivotTable = wsPivot.PivotTableWizard(pivotCache, _
                                              ws.Cells(1, 1), _
                                              ws.Cells(1, 2), _
                                              ws.Cells(1, 3))
    ' Organize Pivot Table Fields
    pivotTable.PivotFields("Sales Rep").Orientation = xlRowField
    pivotTable.PivotFields("Month").Orientation = xlColumnField
    pivotTable.PivotFields("Sales Amount").Orientation = xlDataField
    pivotTable.PivotFields("Sales Amount").Function = xlSum
End Sub

Explanation:

• We define the data range that contains the dataset.
• Create a pivot cache and then use the PivotTableWizard method to create a new pivot table on a separate sheet.
• Set the field orientation for rows (Sales Rep), columns (Month), and data (Sales Amount) to display the sum of sales.

2. Unpivoting Data (Turning Columns into Rows)

Problem: You have a wide dataset, and you want to transform it into a long format (unpivot the data).

Example Data:

Desired Output:

VBA Code for Unpivoting Data:

Sub UnpivotData()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim targetRow As Long
    Dim monthName As String
    Dim salesAmount As Double
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Start populating the new unpivoted data below the existing data
    targetRow = lastRow + 2
    ' Write headers for unpivoted data
    ws.Cells(targetRow, 1).Value = "Sales Rep"
    ws.Cells(targetRow, 2).Value = "Month"
    ws.Cells(targetRow, 3).Value = "Sales Amount"
    targetRow = targetRow + 1
    ' Loop through the data to unpivot
    For i = 2 To lastRow
        For j = 2 To lastCol
            monthName = ws.Cells(1, j).Value
            salesAmount = ws.Cells(i, j).Value
            ws.Cells(targetRow, 1).Value = ws.Cells(i, 1).Value ' Sales Rep
            ws.Cells(targetRow, 2).Value = monthName ' Month
            ws.Cells(targetRow, 3).Value = salesAmount ' Sales Amount
            targetRow = targetRow + 1
        Next j
    Next i
End Sub

Explanation:

• We loop through each row and column of the original dataset.
• For each combination of Sales Rep and Month, we create a new row in the output table with the corresponding month and sales amount.
• The data is now in a long format, suitable for analysis or further transformations.

3. Cleaning Data (Removing Unwanted Characters)

Problem: Your dataset contains unwanted spaces or special characters, and you want to clean the data.

Example Data:

VBA Code for Cleaning Data:

Sub CleanData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cell As Range
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Loop through each row to clean data
    For i = 2 To lastRow
        ' Clean Name - Remove special characters and extra spaces
        Set cell = ws.Cells(i, 1)
        cell.Value = Trim(Replace(cell.Value, "@", ""))
        cell.Value = Trim(Replace(cell.Value, "!", ""))
        ' Clean Address - Remove special characters
        Set cell = ws.Cells(i, 3)
        cell.Value = Trim(Replace(cell.Value, "#", ""))
    Next i
End Sub

Explanation:

• We loop through the rows and clean up the unwanted characters (like @, !, #, etc.) and extra spaces in the Name and Address columns.
• The Trim() function removes leading and trailing spaces, and the Replace() function is used to replace unwanted characters.

4. Complex Filtering (Applying Multiple Criteria)

Problem: You need to filter a dataset based on multiple conditions (e.g., sales greater than a certain value and from a specific region).

Example Data:

VBA Code for Complex Filtering:

Sub FilterData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim salesAmount As Double
    Dim region As String
    ' Set worksheet reference
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Loop through each row to apply the filter criteria
    For i = 2 To lastRow
        salesAmount = ws.Cells(i, 3).Value
        region = ws.Cells(i, 2).Value       
        ' Only keep rows where Sales Amount > 200 and Region is North
        If salesAmount > 200 And region = "North" Then
            ws.Rows(i).Hidden = False
        Else
            ws.Rows(i).Hidden = True
        End If
    Next i
End Sub

Explanation:

1. We loop through the dataset and apply a filter where the Sales Amount is greater than 200, and the Region is « North. »
2. Rows that do not meet these criteria are hidden.

Conclusion

These are just a few of the advanced data transformation techniques you can implement using VBA in Excel. With these methods, you can pivot and unpivot your data, clean it, and apply complex filters to make your dataset more useful for analysis. VBA allows you to automate these tasks, saving you time and ensuring consistency.