Transpose Multiple Columns into One Column
This post will guide you how to transpose multiple columns into a single columns with multiple rows in Excel. How do I put data from multiple columns into one column with Excel formula; How to transpose columns into single column with VBA macro in excel.
Assuming that you have a data list in range B1:D4 contain 3 columns and you want to transpose them into a single column F. just following the below two ways.
Transpose Multiple Columns into One Column with Formula
You can use the following excel formula to transpose multiple columns that contain a range of data into a single column F:
#1 type the following formula in the formula box of cell F1, then press enter key.
=INDEX($B$1:$D$4,1+INT((ROW(B1)-1)/COLUMNS($B$1:$D$4)),MOD(ROW(B1)-1+COLUMNS($B$1:$D$4),COLUMNS($B$1:$D4))+1)
#2 select cell F1, then drag the Auto Fill Handler over other cells until all values in range B1:D4 are displayed.
#3 you will see that all the data in range B1:D4 has been transposed into single column F.
Transpose Multiple Columns into One Column with VBA Macro
You can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly. Just do the following steps:
#1 click on “Visual Basic” command under DEVELOPER Tab.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 paste the below VBA code into the code window. Then clicking “Save” button.
Sub transposeColumns()
Dim R1 As Range
Dim R2 As Range
Dim R3 As Range
Dim RowN As Integer
wTitle = "transpose multiple Columns"
Set R1 = Application.Selection
Set R1 = Application.InputBox("please select the Source data of Ranges:", wTitle, R1.Address, Type:=8)
Set R2 = Application.InputBox("Select one destination single Cell or column:", wTitle, Type:=8)
RowN = 0
Application.ScreenUpdating = False
For Each R3 In R1.Rows
R3.Copy
R2.Offset(RowN, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
RowN = RowN + R3.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
#5 back to the current worksheet, then run the above excel macro. Click Run button.
#6 select the source data of ranges, such as: B1:D4
#7 select one single cell in the destination Column, such as: F1
#8 let’s see the last result.
Related Functions
- Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… - Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer. And the INT function rounds down, so if you provide a negative number, the returned value will become more negative.The syntax of the INT function is as below:= INT (number)… - Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])…. - Excel Columns function
The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)…. - Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...
Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...
Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...
Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...
In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...
Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight ...
When counting the number of times for objects appear in a list or a range, we usually record the duplicate value only once and ignore the redundant ones. We cannot apply formula with only one function to implement this in ...
This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...
In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...
We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the ...
