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:

## 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