This post will guide you how to transpose data from rows to column with a formula in excel using both a VBA code and a formula. How do I transpose every N rows from one column to multiple columns in Excel.
In Excel, Transposing data is a common task, but when you want to transpose every N rows of data into multiple columns, it can be a bit trickier. In this post, we’ll show you how to use a VBA code and a formula to accomplish this task easily.
Assuming that you have a list of data in range A1:A10 in column A, and you want to transpose every 2 rows from column A to Mulitple columns. for example, you want to transpose range A1:A2 to C1:D1, A3:A4 to C2:D2. How to do it.
Table of Contents
1. Video: transpose Every N Rows of Data into Multiple Columns
Here’s a video tutorial on how to transpose every N rows of data into multiple columns in Excel using a formula and VBA code.
2. Transpose Every N Rows of Data into Multiple Columns Using Excel Formula
Type this formula into cell C1, and press
Enter key on your keyboard, and then drag the AutoFill Handle to CEll D1.
Then you need to drag the AutoFill Handle in cell D1 down to other cells until
value 0 is displayed in cells.
3. Transpose Every N Rows of Data into Multiple Columns with VBA Code
You can also use VBA code to transpose every N rows of data into multiple columns in Excel. Here’s a step-by-step guide:
Step1: Open the Excel workbook you want to work with and press “Alt + F11” to open the Visual Basic Editor.
Step2: In the Visual Basic Editor, click on “Insert” from the menu bar and select “Module” to create a new module.
Step3: Copy and paste the following code into the module window.
Sub TransposeRows_excelhow() Dim i As Long Dim j As Long Dim k As Long Dim LastRow As Long Dim NumCols As Long Dim DataRange As Range Dim DestRange As Range ' Set the number of rows to transpose into each column NumCols = Application.InputBox("Enter the number of rows to transpose into each column:") ' Select the range of cells to transpose On Error Resume Next Set DataRange = Application.InputBox("Select the range of cells to transpose:", Type:=8) On Error GoTo 0 If DataRange Is Nothing Then Exit Sub ' Select the destination range for the transposed data On Error Resume Next Set DestRange = Application.InputBox("Select the destination range for the transposed data:", Type:=8) On Error GoTo 0 If DestRange Is Nothing Then Exit Sub LastRow = DataRange.Rows.Count k = 0 For i = 1 To LastRow Step NumCols k = k + 1 For j = 0 To NumCols - 1 DestRange.Offset(j, k - 1).Value = DataRange.Offset(i + j - 1, 0).Value Next j Next i End Sub
Step4: Press “F5” or click on the “Run” button in the toolbar to run the code.
Step5: Enter the number of rows to transpose into each column, such as: 2,Click OK button.
Step6: Select the range of cells to transpose, such as: selecting range A1:A10. Click OK button.
Step7: Select the destination range for the transposed data, such as: Cell C1. Click on Ok button.
Step8: Once you have entered the required information, the selected range of cells will be transposed into multiple columns according to the number of rows you specified.
Step9: select the transposed data and press Ctrl +C to copy it. Then right click on a black cell and select “Transpose” from the Paste option menu list.
Step10: You can quickly and easily transpose every N rows of data into multiple columns using the TRANSPOSE function in Excel.
Transposing every N rows of data into multiple columns in Excel can be done using either a VBA code or a formula. Both the VBA code and the formula are effective methods for transposing every N rows of data into multiple columns in Excel. The best method to use will depend on the specific needs of your project and your level of comfort with coding in VBA.
5. 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 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 COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….