Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Copy or Transpose data from ROWS to COLUMNS by formula in Excel

Copying or transmitting data, from horizontal of rows in the vertical columns in Excel

About basic TRANSPOSE function a I wrote. In the formulas that are used in this tutorial, I combined the Excel functions: ROW, OFFSET, VLOOKUP, MOD, ROUNDDOWN, INT, SEARCH, RIGHT, etc.

Copy or Transpose Rows to Column, on to another Worksheet in Excel by formula

In this tutorial I will try to show beginners in Excel, how can we make copies of data from multiple rows into several columns. Not unusual that users of Excel, sometimes meet each with a problem and they need data from rows copy into column, but also to perform vertical copying and every data from a specific row, belong to certain information from the column, the respective range of cells in Excel "table" .

For this tutorial and example, you look at the situation and arrangement of data in "Sheet1" in the figure below. You notice that "Excel table" contains in column 'A' names of persons who belong to the data in the same row but in multiple columns.

Copy and trasnpose data from table rows to the column

Our goal is to copy (on Sheet2) all data, from of rows for a specific person's name but so that the name appears several times for each month, where we will join the corresponding data from a specific column. (see figure below).

Transpose Rows to Column by formula - OFFSET function

In the picture below you will see four columns. I am separated columns for easier understanding. In the column 'A', is a formula that vertical copying shows the names persons, as many times as there are columns in the table on Sheet1. As the table on 'Sheet1' have 12 columns (the names of months in a year), this formula will display the names of 12 persons. The formula is unique in that it is entered in the the first cell 'A2' and copy it to the last required row.

The formula in the cell 'A2' is as follows: (This offset formula, you need to copy to the last row)

=OFFSET(Sheet1!$A$2;ROUNDDOWN((ROW(A1)-1)/12;0);0)

You notice number 12 in the formula. This number indicates the number of columns on Sheet1 (B:M).

Data copied from Sheet1 to Sheet2, so that the data is transposed from rows into columns

Column 'C' => first way to resolve transposing of data from the rows in the column
The first way to resolve for displaying to view related information for the condition in column 'A', the creation of more individual formulas, using the OFFSET function. Thus, the condition is cell column 'A'. In the picture above, you will see the column 'C'. In this column, there is a 12 formula. These formulas are located in a range of cells 'C2:C13'. Each formula is unique. Here we can not set up a unique formula, so it is to copy down to the last row. We need to create twelve formula and copy them in the one group.

These formulas are as follows:
  • The formula in the cell 'C2': =OFFSET(Sheet1!$B$2;ROUNDDOWN((ROW(A1)-1)/12;0);0)
  • The formula in the cell 'C3': =OFFSET(Sheet1!$C$2;ROUNDDOWN((ROW(A2)-1)/12;0);0)
  • The formula in the cell "C4": =OFFSET(Sheet1!$D$2;ROUNDDOWN((ROW(A3)-1)/12;0);0)
  • ...
  • The formula in the cell "C13": =OFFSET(Sheet1!$M$2;ROUNDDOWN((ROW(A12)-1)/12;0);0)

After creating these twelve formulas, you select the range of cells 'C2:C13' and complete a selected group of cells, you copy down to the last row. In this way the formula for each condition in column 'A' and the name, as a result of back data from the corresponding row, but each data from columns 'B:M', particularly in the cells of one under the other.

Column 'E' => another way to solve transposing of data from the rows in the column

This problem, Transpose rows to the column, can be solved using only a single formula that can be to copy from the cells 'E2' to the last row. The formula in column 'E' uses a combination of nested Excel functions and the main functions of the OFFSET.

How do I solve the problem with a formula that is easy to be copied down?

For more details and a better understanding of the following explanations, see the picture and the text at the end of this tutorial.

Formula-1

The problem that is tormenting me, I wanted to create a unique formula and copy it down, but also to get valid results.
I'm going to solve the problem of less formula and inserting into a larger formula, etc. So I successfully created a single long formula, which has solved the problem transpose rows into columns. In the first step, I created a formula to me as a result of back label for the rows and columns, ie numbers from 1-12 ("C"&MOD(ROW()-1;12), number 12 in the formula indicates the twelve columns on Sheet1, and also next ("R"&INT((ROW()-1)/12)+1, the number 12 indicates in formula twelve rows on Sheet1. The formula returns the numbers from 1 to 12 is as follows. (R1C1, R1C2, R1C3 ... R1C12)

="R"&INT((ROW()-1)/12)+1&"C"&MOD(ROW()-1;12)+1

This formula above, as a result returns text, in the the form of R1C1, R1C2 ... R1C12.
Here I want to emphasize that the formula may vary from above in the this tutorial, but it depends on where the first row set formula for copying. Just try this formula in the several different cell rows, by copying down. If you want to manipulate this formula, then instead of the ROW() function, define the row from which you want to search results. for example, ROW(A1) or ROW(A7) and so on. But then refer to the latest issue (+1)

Formula-2

The following basic formula that I used is: =RIGHT(A1;LEN(A1)-SEARCH("C";A1))

In the formula above, I am nestled instead of each address cell A1, the Formula-1. (see above). And this symbolic visually looks like.

=RIGHT(Formula-1;LEN(Formula-1)-SEARCH("C";Formula-1))

So, after a nested formula now created a new formula is as follows:

=RIGHT("R"&INT((ROW(A1)-1)/12)+1&"C"&MOD(ROW(A1)-1;12)+1;LEN("R"&INT((ROW(A1)-1)/12)+1&"C"&MOD(ROW(A1)-1;12)+1)-SEARCH("C";"R"&INT((ROW(A1)-1)/12)+1&"C"&MOD(ROW(A1)-1;12)+1))-1

Formula-3

And finally we come to the final formula OFFSET function.
The syntax for function Offset is the following: =OFFSET(reference;rows;cols;height;width)
The basic formula above is the following: =OFFSET(Sheet1!$B$2;ROUNDDOWN((ROW(A1)-1)/12;0);0)

=OFFSET(Sheet1!$B$2;ROUNDDOWN((ROW(A1)-1)/12;0);Formula-2)

or

=OFFSET(Sheet1!$B$2;ROUNDDOWN((ROW(A1)-1)/12;0);RIGHT(Formula-1;LEN(Formula-1)-SEARCH("C";Formula-1)))

Therefore, ultimately in cell formulas 'E2' is the following:
(If you copy this formula below in the your Excel, the formula must be in the a single line), set the formula in the cell 'E2' and copy down to the last row

=OFFSET(Sheet1!$B$2;ROUNDDOWN((ROW(A1)-1)/12;0);RIGHT("R"&INT((ROW(A1)-1)/12)+1&"C"&MOD(ROW(A1)-1;12)+1;LEN("R"&INT((ROW(A1)-1)/12)+1&"C"&MOD(ROW(A1)-1;12)+1)-SEARCH("C";"R"&INT((ROW(A1)-1)/12)+1&"C"&MOD(ROW(A1)-1;12)+1))-1)

Instead argument column number zero (0), I am more nested functions and completed a single formula, where the argument instead of the number of columns, by copying down to change the value of 2,3,4 ... 12

Transpose data from Rows to Column by formula VLOOKUP function


Column 'G' => third way to resolve transposing of data from the rows in the column

In a similar way I solved the same problem with one VLOOKUP formula. This formula I am not going to explain, because it is similar to the mode as in previous OFFSET formula.

The formula in the cell 'G2' is as follows: (If you copy this formula below in the your Excel, the formula must be in the a single line), set the formula in the cell "G2" and copy down to the last row. Here I want only to point out the following.

Syntax VLOOKUP function as follows:

=VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

Instead argument 'col_index_num', which is in fact the number of columns I inserted a nested formula that when the vertical copy returns the number of columns.

=VLOOKUP(A2;Sheet1!$A$2:$M$13;RIGHT("R"&INT((ROW(A1)-1)/100)+1&"C"&MOD(ROW(A1)-1;12)+1;LEN("R"&INT((ROW(A1)-1)/100)+1&"C"&MOD(ROW(A1)-1;12)+1)-SEARCH("C";"R"&INT((ROW(A1)-1)/100)+1&"C"&MOD(ROW(A1)-1;12)+1))+1;FALSE)
And finally I want to emphasize that the formula may vary from above in the this tutorial, but it depends on where the first row set formula for copying.

How does the address formula R1C1, which returns the number of columns in a vertical copying, as a result
At the end of this tutorial, I want to explain the basic formula, by which I am by copying vertical as a result of returning the number of columns.

In the picture below you see the column 'A'.
The formula in cell 'A2' is the following: (copied down). This formula returns a text R1C1 and by copying down continues to return results R1C2,R1C3 etc. until R1C12. Further by copying the following text as a result of the formula to return R2C1,R2C2, ... R2C12 it if in the circle.

="R"&INT((ROW()-1)/12)+1&"C"&MOD(ROW()-1;12)+1

If this formula I put in a cell 'A20', even though I wanted to go back as a result of R1C1 then it would look like this

="R"&INT((ROW(A20)-19)/12)+1&"C"&MOD(ROW(A20)-20;12)+1

In column 'C' you note text addresses the cell identical to the data in column 'A'. This column 'C', I have set just so beginners easier to explain what I'm still doing. In column 'C' you notice red numbers 1-12. These numbers are important to us because they extract from the text and use them for the column number (argument Col_Num) when vertical copying.

In order to extract the last characters (numbers) after the letter 'C' in the text, I have used a few formulas that are in the columns 'E, F and G'.

The formula in cell 'E2' is the following:

=MID(SUBSTITUTE(A1;"C";"^";LEN(A1)-LEN(SUBSTITUTE(A1;"C";"")));FIND("^";SUBSTITUTE(A1;"C";"^";LEN(A1)-LEN(SUBSTITUTE(A1;"C";""))))+1;256)

The formula in cell 'F2' is the following:

=RIGHT(A1;LEN(A1)-FIND("C";A1))

The formula in cell 'G2' is the following:

=RIGHT(A1;LEN(A1)-SEARCH("C";A1))

All three above mentioned formula, perform extract the last of character after the letter 'C' in the text 'R1C1'.
To solve the problem, Transpose Rows to Column I used this last formula with functions RIGHT/LEN/SEARCH. Here I would note the difference between the last two formulas. In the final result, depending on the conditions that we seek, a formula that contains a function SEARCH, differs from the formula that contains the function FIND.
The SEARCH function does not distinguish uppercase and lowercase characters into consideration will take a lot or a little letter "C/c", while FIND function is case sensitive. If you set the condition of a small letter "c" function does not return the correct result while the search function returned the correct result.
R1C1, R1C2, R1C3 address cells in Excel

About basic TRANSPOSE function an I wrote on the respective link.