Excel convert data from column to row

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”.

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *