How to Convert Matrix Style Table to Three Columns in Excel

MS Excel Microsoft Technologies Computers

Sometimes in Excel, you may have needed to convert a matrix-style table to a column-style table. Any table with m rows and n columns is referred to as a matrix style table, and any table with m rows and three columns is referred to as a three column table in Excel. Read this tutorial to learn how you can convert a matrix-style table to a three-column table in Excel. We can complete this process with the help of a VBA application, as it can’t be completed directly in Excel.

Converting a Matrix Style Table to Three Columns in Excel

Here, we will first create a VBA module and then run the code to complete the task. Let's look at a simple procedure for converting a matrix-style table to a three-column table in Excel.

Step 1

Let us consider any Excel sheet that contains a matrix-style table similar to the below image.

Then right-click on the sheet name and select view code to open the VBA application, then click on insert and select module.

Right click > View code > Insert > Module

Step 2

Then type the below-mentioned programme into the text box, as shown in the below image.

Program

Sub ConvertTable()
'Updated By Nirmal
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "Convert to Column"
Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8)
Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8)
Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8)
Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set xWs = Rng.Worksheet
k = 1
xColumns = rRng.Column
xRow = cRng.Row
For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1
    For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1
        outRng.Cells(k, 1) = xWs.Cells(i, xColumns)
        outRng.Cells(k, 2) = xWs.Cells(xRow, j)
        outRng.Cells(k, 3) = xWs.Cells(i, j)
        k = k + 1
    Next j
Next i
End Sub