Excel VBA Convert Text in Columns and Rows into Matrix Table

This Excel VBA tutorial explains how to convert text in columns and rows into Matrix Table. The Matrix table will display text instead of aggregated numbers.

You may also want to read:

Access Crosstab Query

Excel VBA Convert Text in Columns and Rows into Matrix Table

Using Excel Pivot Table, it is easy convert data into a Matrix Table with the help of Aggregate Functions such as Count, Sum, standard deviation. The information in the Matrix Table is displayed in aggregated numbers. Below is an example of a Matrix Table using aggregated function.

However if you want to create a Matrix Table in which you want to display nominal data (text) instead of aggregated numbers, Pivot Table is not technically possible.

In this tutorial, I am going demonstrate how to convert text in columns and rows into Matrix Table to display nominal data using Excel VBA.

VBA Code – Convert Columns and Rows into Matrix Table

Press ALT+F11 and insert the below VBA Procedure into a Module. Note that the you probably need to customize your own code in order to fit your needs, the below Procedure is just an example to show you how it can be done.

Public Sub convertMatrix()
    'assume data worksheet contains employee info, while matrix worksheet is the end result
    For r = 2 To Worksheets("data").Range("A" & Rows.Count).End(xlUp).Row
        Name = Worksheets("data").Range("A" & r)
        dept = Worksheets("data").Range("B" & r)
        
        Title = Worksheets("data").Range("C" & r)
        salary = Worksheets("data").Range("D" & r)
        grade = Worksheets("data").Range("E" & r)
    
        'search for department column number in the matrix table
        For c = 1 To Worksheets("matrix").Range("IV" & 1).End(xlToLeft).Column
            If Worksheets("matrix").Cells(1, c).Value = dept Then
                matrixCol = c
                Exit For
            End If
        Next c
        
        'search for grade row in the matrix table
        For g = 2 To Worksheets("matrix").Range("A" & Rows.Count).End(xlUp).Row
            If Worksheets("matrix").Cells(g, 1) = grade Then
                matrixRow = g
                Exit For
            End If
        Next g
        
        'Convert columns and rows into matrix table
        If Worksheets("matrix").Cells(g, c).Value <> "" Then
            Worksheets("matrix").Cells(g, c).Value = Worksheets("matrix").Cells(g, c).Value & vbCrLf & vbCrLf & Name & vbCrLf & Title & vbCrLf & salary
        Else
            Worksheets("matrix").Cells(g, c).Value = Name & vbCrLf & Title & vbCrLf & salary
        End If
    Next r
End Sub

Example – Convert Columns and Rows into Matrix Table

Suppose data worksheet contains the employee data that you want to convert into matrix table.

The employees highlighted in yellow are in the same department and of the same grade, I will demonstrate how it will display in the matrix table.

 

matrix worksheet contains the layout of the matrix, where we want to put the employee data into a matrix of Department and Grade.

 

Now execute the Procedure, employees and their info are put into the matrix table.

For employee Cat and Cathy, since they are in the same department and they are of the same grade, both their info are put into Cell B4, separated by a blank row.

Outbound References

https://support.office.com/en-us/article/Transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419F2E3-BEAB-4318-AAE5-D0F862209744