Converting a row of data (from Sheet 1) to a column (in Sheet 2)

N

NJOHNSON

New Member
Joined
Apr 11, 2017
Messages
6
I have a large data set that I need to move from (Sheet 1) to (Sheet 2). The data is in columns in Sheet 1, but I want to put it into rows on Sheet 2.

If possible, I'd like to make it so that if I modify the data in sheet 1, it will automatically modify the data in sheet 2.

Any help is greatly appreciated.

Example of my problem:

Convert (sheet 1 data in columns)

A 1
B 2
C 3
D 4

To sheet 2 in rows as follows:

A B C D
1 2 3 4
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Put this formula in cell A1 on Sheet 2...

=INDEX(Sheet1!$A:$Z,COLUMN(),ROWS($1:1))

and change the highlighted "Z" to the column letter designation for the last data column on Sheet 1, then copy the formula across for as many columns as you have rows of data on Sheet1, then copy all those cells down for as many rows as there are data columns on Sheet1.
 
Upvote 0
Rick Rothstein said:
Put this formula in cell A1 on Sheet 2...

=INDEX(Sheet1!$A:$Z,COLUMN(),ROWS($1:1))

and change the highlighted "Z" to the column letter designation for the last data column on Sheet 1, then copy the formula across for as many columns as you have rows of data on Sheet1, then copy all those cells down for as many rows as there are data columns on Sheet1.
Click to expand...

Hi Rick,

Much appreciated for the response. If you would please be so kind as to indicate what each of the variables does as I'm trying to use this formula, but with limited success thus far.

I have 2 columns with 390 pieces of data in each (paired) in Sheet 1.
I have 2 Rows that I want to put these pieces of data in so they stack on top of each other (to keep the pairs intact), but I keep on getting #REF! in most of the boxes.
 
Upvote 0
NJOHNSON said:
I have 2 columns with 390 pieces of data in each (paired) in Sheet 1.
I have 2 Rows that I want to put these pieces of data in so they stack on top of each other (to keep the pairs intact), but I keep on getting #REF! in most of the boxes.
Click to expand...
What is the name of the sheet that your 2 columns of data are on... "Sheet1" like I assumed or "Sheet 1" like you wrote above. Also, what cell is your first data item in?
 
Last edited:
Upvote 0
Rick Rothstein said:
What is the name of the sheet that your 2 columns of data are on... "Sheet1" like I assumed or "Sheet 1" like you wrote above. Also, what cell is your first data item in?
Click to expand...

Sheet 1 is labelled 2. Data Entry

I want to start pulling data from Cell C7 to C390 (column1) and Cell D7 to D390 (column2).

I want to put this data onto my 2nd worksheet labelled (4. Individual Competency Level) starting at cells D8 and D9 all the way across the rows going right.

I almost had it, but the data is 3 columns off.

I really appreciate your help.
 
Upvote 0
NJOHNSON said:
Sheet 1 is labelled 2. Data Entry

I want to start pulling data from Cell C7 to C390 (column1) and Cell D7 to D390 (column2).
Click to expand...
You should have included this information in your original message as there is no way we could have guessed it on our own.



NJOHNSON said:
I want to put this data onto my 2nd worksheet labelled (4. Individual Competency Level) starting at cells D8 and D9 all the way across the rows going right.
Click to expand...
Put this formula in cell D8 and copy it down to D9, then copy those two cells over to (if I calculated correctly) Column OA...

=INDEX('2. Data Entry'!$C:$D,6+COLUMN(),ROWS($1:1))
 
Upvote 0
Just tried it. It starts with the data from the 4th row of the columns from sheet 1 (2. Data Entry).

I'm thinking this is because in Sheet 2 there are 3 columns before the cell I paste into?

Thanks for all the help - I know I'm probably frustrating you with my explanations. I normally use excel for statistical modelling, but have never had to perform this type of function before.
 
Upvote 0
I got it to work. I had to change the formula to this:

=INDEX('2. Data Entry'!$C:$D,3+COLUMN(),ROWS($1:1))

Changed the 6 to a 3 and it worked. Thank you a million times over! Not sure how I can repay the favour, but I'll pay it forward in your honour.
 
Upvote 0
NJOHNSON said:
I got it to work. I had to change the formula to this:

=INDEX('2. Data Entry'!$C:$D,3+COLUMN(),ROWS($1:1))

Changed the 6 to a 3 and it worked. Thank you a million times over! Not sure how I can repay the favour, but I'll pay it forward in your honour.
Click to expand...
I think I screwed up the offsets when I tried to adjust my original formula to your "new" layout. This is one of the reasons you should give us all the information about your actual layout as its easier to design to that then to design to an assumed location and then have to move it afterwards. In any event, I am glad you got it all straightened out.
 
Upvote 0
It's my first time posting on these forums, but I will be sure to include all of the details of my layout next time I ask a question.

Again, thanks a million, Rick! You have yourself a wonderful evening!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
120
JMC57
J
B
  • Question
Getting error if linked cell row deleted in different sheet of large data.
Replies
3
Views
117
Peter_SSs
Peter_SSs
B
  • Question
Copying Data from Sheet 1 to Sheet 2 and adding rows- Code Needed
2 3 4
Replies
30
Views
821
mumps
mumps
T
  • Solved
Move data from one sheet to another automatically
Replies
3
Views
127
kevin9999
K
T
  • Solved
VBA code cannot find sheet name
2
Replies
11
Views
183
jolivanes
jolivanes

Forum statistics

Threads
1,215,051
Messages
6,122,871
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top