A B C
E F G
H I J
A B C E F G H I J
How to change from above to single row?
To post as a guest, your comment is unpublished.· 1 years agoHave you been able to accomplish this? I'm having the same problem.
How to join multiple rows and columns into a single long row? Maybe, it seems easy for you, because you can copy them one by one and join them into a row manually. But it will be time consuming and tedious if there are hundreds of rows and columns. Here I will talk about some quick tricks to solve it.
Supposing you have a range of data as following screenshot shows, you can convert the range data into one row in a new worksheet with a long formula.
Please apply the formula as this:
1. In a new worksheet of the active workbook, click cell A1, copy and paste this formula: =OFFSET(Sheet1!$A$1,((ROW()-1)*5)+(FLOOR(COLUMN()-1,4)/4),(COLUMN()-1)-(FLOOR(COLUMN()-1,4)))
Note: Sheet1!$A$1 is the worksheet and range references that you want to use.
ROW()-1)*5 in the above formula, 5 stands for the row number; and COLUMN()-1,4)/4, the 4 stands for the column number. You can change them as your need.
2. Then drag the fill handle over to right until number 0 is appeared. In this case, all the data in the range has been transposed into a single row in a new worksheet. See screenshot:
The following VBA code also can help you convert a range of data to one row.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module window.
Sub TransformOneRow() 'Updateby20131120 Dim InputRng As Range, OutRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Ranges to be transform :", xTitleId, InputRng.Address, Type:=8) Set OutRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8) Application.ScreenUpdating = False xRows = InputRng.Rows.Count xCols = InputRng.Columns.Count For i = 1 To xRows InputRng.Rows(i).Copy OutRng Set OutRng = OutRng.Offset(0, xCols + 0) Next Application.ScreenUpdating = True End Sub
3. Then press F5 key to run the code, a dialog is popped out for you to select a range of contents you want to convert into a row, and click Ok, another popped-up dialog is for you to select a single cell to put out the result. See screenshots:
Then Click Ok, the contents in the selected range are converted into a row. See screenshot:
Note: In Set OutRng = OutRng.Offset(0, xCols + 0), you can change 0 to any number to meet your need.
For exampel, if you want to separate the results accroding to the original rows by one column, you can change Set OutRng = OutRng.Offset(0, xCols + 0) to Set OutRng = OutRng.Offset(0, xCols + 1), the results are shown as following:
It seems the above two methods are somewhat difficult for us Excel beginner, here I will talk about a handy tool-Kutools for Excel for you.
With the utility Transform Range of Kutools for Excel, you can quickly transform a range to a single row, please do as follows:
1. Select the range that you want to transpose.
2. Click Kutools > Range Converter > Transform Range, see screenshot:
3. In the Transform Range dialog box, select Range to single row option, see screenshot:
4. Then click OK, and click a cell to put the result from the pop out box.
5. Click OK, and the data in the range has been transposed into a single row. See screenshots:
If you want to know more about this feature, please visit Transform Range.