This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row).
You may also want to read:
Excel VBA consolidate multiple workbooks into one workbook
Excel VBA combine worksheets columns into one worksheet
Excel convert data from column to row
Different database have different structure. Some database put similar data in the same column with different rows, while some database put data in different columns. .Let’s take an example.
Type 1: all payment types are arranged in the same column
Employee ID | Payment Type | Amount |
1 | Salary | 10000 |
1 | Allowance | 1000 |
2 | Salary | 10000 |
3 | Salary | 10000 |
4 | Salary | 10000 |
Type 2: each column for each payment type
Employee ID | Salary | Allowance |
1 | 10000 | 1000 |
2 | 10000 | |
3 | 10000 | |
4 | 10000 |
In this tutorial, I will demonstrate how to use VBA to convert data from column to row (from type 2 to type 1).
VBA Code – convert data from column to row
Please note that when I wrote this Macro, I only tested the scenarios in my below examples. For any other exceptional scenarios that I am not aware, my Macro may not function probably (I don’t know what I don’t know).
Press ALT+F11 and then paste the below code in a new Module
Public Sub convert() 'the column data that you want to repeat (such as name) as you move the data down the row ReptColSt = "A" ReptColEd = "C" 'column that contains the data that you want to move down the row. This includes the first column that acts as the header for other moved data dataColSt = "D" dataColEd = "I" 'the number of columns the you want to move as a set to a single row noOfDataCol = 1 colLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row ReptColStNum = Range(ReptColSt & 1).Column ReptColEdNum = Range(ReptColEd & 1).Column noOfReptCol = ReptColEdNum - ReptColStNum + 1 dataColStNum = Range(dataColSt & 1).Column dataColEdNum = Range(dataColEd & 1).Column lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row For r = 2 To colLastRow For c = dataColStNum To dataColEdNum If c = dataColStNum Then For k = 2 To lastRow Cells(1, dataColStNum).Copy Cells(k, dataColEdNum + 1) Next ElseIf (c - dataColStNum - noOfDataCol) Mod noOfDataCol = 0 Then nextRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1 Range(ReptColSt & r & ":" & ReptColEd & r).Copy Range(ReptColSt & nextRow) Cells(1, c).Copy Cells(nextRow, dataColEdNum + 1) For n = 0 To noOfDataCol - 1 Cells(r, c + n).Copy Cells(nextRow, dataColStNum + n) Next End If Next c Next r Cells(1, dataColEdNum + 1).Value = "Original Column" 'to delete the original data columns Range(Cells(1, dataColStNum + noOfDataCol), Cells(1, dataColEdNum)).EntireColumn.Delete 'to delete rows with empty records lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row For m = lastRow To 2 Step -1 counter = 0 For n = dataColStNum To dataColStNum + noOfDataCol - 1 If Cells(m, n).Value = "" Then counter = counter + 1 End If If counter = noOfDataCol Then Rows(m).EntireRow.Delete End If Next n Next m End Sub
Convert all columns to a single column
A little explanation for this code with an example. In the below Table, my goal is to move each salary and bonus column from column D to I to the same column (column D). For each newly added row, I want column A to C to repeat.
In the parameter line of code, I need to set the followings
'the column data that you want to repeat (such as name) as you move the data down the row ReptColSt = "A" ReptColEd = "C" 'column that contains the data that you want to move down the row. This includes the first column that acts as the header for other moved data dataColSt = "D" dataColEd = "I" 'the number of columns the you want to move as a set to a single row noOfDataCol = 1
Run the Macro and get the below result.
Column E is a new column created to indicate which column the data in column D originally came from. Column D header has to be manually change as it is the original header before conversion, so it is more appropriate to change it to “Amount”.
Convert all columns to a multiple columns
Suppose I want to convert all data columns to fit into column D (Salary) and column E (Bonus). Use the same Macro as above but update the below parameter to 2.
'the number of columns the you want to move as a set to a single row
noOfDataCol = 2
Run the Macro and get the below result.
Similar to the above example, column F is the new column to indicate where column D and E data originally came from. Column D and E headers should be renamed appropriately, such as “Salary” and “Bonus”.