Columns to rows

rdeters · May 8th, 2002, 10:22 AM

What I am trying to do is take a 7 column by 500 row grid of information from a worksheet and populate it into a grid that is 1 column by 3500 rows on another worksheet. In other words 7 columns of data will become 1 column with 7 rows. I can not use the copy & paste function, because the original grid is initially blank. While info is being entered into the grid, I want it to be displayed on the next worksheet in the format I described. I have tried the transpose function and it does not seem to work for me, I may be doing something wrong though. Any help would be appreciative.

Here is an example of the formulas:
cell A1 : ='Instrument Tags'!T3
cell A2 : ='Instrument Tags'!U3
cell A3 : ='Instrument Tags'!V3
cell A4 : ='Instrument Tags'!W3
cell A5 : ='Instrument Tags'!X3
cell A6 : ='Instrument Tags'!Y3
cell A7 : ='Instrument Tags'!Z3

When I try to carry these over to the next 7 rows, here is what the formulas look like:
cell A8 : ='Instrument Tags'!T10
cell A9 : ='Instrument Tags'!U10
cell A10 : ='Instrument Tags'!V10
cell A11 : ='Instrument Tags'!W10
cell A12 : ='Instrument Tags'!X10
cell A13 : ='Instrument Tags'!Y10
cell A14 : ='Instrument Tags'!Z10

But this is what I am assuming it sould look like:
cell A8 : ='Instrument Tags'!T4
cell A9 : ='Instrument Tags'!U4
cell A10 : ='Instrument Tags'!V4
cell A11 : ='Instrument Tags'!W4
cell A12 : ='Instrument Tags'!X4
cell A13 : ='Instrument Tags'!Y4
cell A14 : ='Instrument Tags'!Z4

rdeters · May 8th, 2002, 02:33 PM

Chris Davison · May 8th, 2002, 02:39 PM

is this the same worksheet as your earlier one ?

http://www.mrexcel.com/board/viewtop...c=7571&forum=2

rdeters · May 8th, 2002, 02:45 PM

Kind of, it is the same file, but I took you're advice from the ealier one and changed the excel file/program a bit to make it less difficult in the end. The problem I am now facing seems to be somewhat easier. But I guess not, considering I haven't figured it out yet. What's your opinion on this Chris?

Chris Davison · May 8th, 2002, 02:54 PM

My opinion is it's tricky.... just this above example is proving tricky (i'm working on it now)

(although I'm no expert by any stretch of the imagination!)

nearly there though....

rdeters · May 8th, 2002, 02:56 PM

A little bit of more input: Looking at the incremental format that excel is generating when I pull down the cells, the various groups go: 3,10,17,24,31,etc. (I of course want 3,4,5,6,7,etc,) The mathmatical formula turns out to be y=((x-3)/7)+3, where x is the row that excel is inputing into the cell when dragged down. In the previos reply that you indicated on the other problem, you said that you could indicate an indirect input for the row number, is this possible with this mathematical formula that I have supplied? I hope this explanation is clear.

Chris Davison · May 8th, 2002, 03:28 PM

purely for this example....

try this in your other sheet, cell A1 :

=INDIRECT("'Instrument Tags'!"&CHAR(SUBSTITUTE(MOD(ROW(),7),0,7)+83)&ROUNDUP((ROW()/7),0)+2)

and just copy down as far as is needed

If anyone can follow what I've done here, I'd appreciate it if you could suggest an easier or tidier way : the logic of transposing coupled with then dropping down a row after each 7th record was a bit of a mind-twister.... hence the ugly formula

but it works...so this should get you started

_________________
Hope this helps,
Chris

be careful to ensure the single quotes around ....instrument tags.... it's sometimes easy to miss them on this board

[ This Message was edited by: Chris Davison on 2002-05-08 14:29 ]

just to test fully : this formula in A1 copied down to row 3486, produces the follwoing result in cell 3486 :

"Z500"

which is the 7th column (column Z) on row 500
from your Instruments Tags sheet

[ This Message was edited by: Chris Davison on 2002-05-08 14:44 ]

Chris Davison · May 8th, 2002, 03:36 PM

Quote:

The mathmatical formula turns out to be y=((x-3)/7)+3

I like your thought processes..... this is exactly the same sort of logic I've been applying to something similar but of a different problem scenario....

[a tip, if you're used to looking at this sort of logic-solving relating to rows : use excel's fractional formats, rather than decimals : it'll maybe help highlight an error in terms of row numbers 11 and 17 as 11/17 rather than an error of 0.647058824 which means nothing !]

Anyhow, hope the above helps, although I suspect it may not be the end of your problem

rdeters · May 8th, 2002, 03:37 PM

Chris, you are the man. Thank you ever so much. I have no idea how you figured this out, but that is pretty good! Thanks again.

Chris Davison · May 8th, 2002, 04:01 PM

no worries...

I'll run through it if you want, step by step

I suspect you *will* follow it given your own input of that mathmatical formula you suggested....

give me a shout and I'll post the individual parts, although it may not be today as it's 11pm here and to be honest this single post has exhausted me !

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 8th, 2002, 10:22 AM	#1
rdeters New Member Join Date: May 2002 Location: Calgary, Canada Posts: 40	What I am trying to do is take a 7 column by 500 row grid of information from a worksheet and populate it into a grid that is 1 column by 3500 rows on another worksheet. In other words 7 columns of data will become 1 column with 7 rows. I can not use the copy & paste function, because the original grid is initially blank. While info is being entered into the grid, I want it to be displayed on the next worksheet in the format I described. I have tried the transpose function and it does not seem to work for me, I may be doing something wrong though. Any help would be appreciative. Here is an example of the formulas: cell A1 : ='Instrument Tags'!T3 cell A2 : ='Instrument Tags'!U3 cell A3 : ='Instrument Tags'!V3 cell A4 : ='Instrument Tags'!W3 cell A5 : ='Instrument Tags'!X3 cell A6 : ='Instrument Tags'!Y3 cell A7 : ='Instrument Tags'!Z3 When I try to carry these over to the next 7 rows, here is what the formulas look like: cell A8 : ='Instrument Tags'!T10 cell A9 : ='Instrument Tags'!U10 cell A10 : ='Instrument Tags'!V10 cell A11 : ='Instrument Tags'!W10 cell A12 : ='Instrument Tags'!X10 cell A13 : ='Instrument Tags'!Y10 cell A14 : ='Instrument Tags'!Z10 But this is what I am assuming it sould look like: cell A8 : ='Instrument Tags'!T4 cell A9 : ='Instrument Tags'!U4 cell A10 : ='Instrument Tags'!V4 cell A11 : ='Instrument Tags'!W4 cell A12 : ='Instrument Tags'!X4 cell A13 : ='Instrument Tags'!Y4 cell A14 : ='Instrument Tags'!Z4

May 8th, 2002, 02:33 PM	#2
rdeters New Member Join Date: May 2002 Location: Calgary, Canada Posts: 40

May 8th, 2002, 02:39 PM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	is this the same worksheet as your earlier one ? http://www.mrexcel.com/board/viewtop...c=7571&forum=2

May 8th, 2002, 02:45 PM	#4
rdeters New Member Join Date: May 2002 Location: Calgary, Canada Posts: 40	Kind of, it is the same file, but I took you're advice from the ealier one and changed the excel file/program a bit to make it less difficult in the end. The problem I am now facing seems to be somewhat easier. But I guess not, considering I haven't figured it out yet. What's your opinion on this Chris?

May 8th, 2002, 02:54 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	My opinion is it's tricky.... just this above example is proving tricky (i'm working on it now) (although I'm no expert by any stretch of the imagination!) nearly there though....

May 8th, 2002, 02:56 PM	#6
rdeters New Member Join Date: May 2002 Location: Calgary, Canada Posts: 40	A little bit of more input: Looking at the incremental format that excel is generating when I pull down the cells, the various groups go: 3,10,17,24,31,etc. (I of course want 3,4,5,6,7,etc,) The mathmatical formula turns out to be y=((x-3)/7)+3, where x is the row that excel is inputing into the cell when dragged down. In the previos reply that you indicated on the other problem, you said that you could indicate an indirect input for the row number, is this possible with this mathematical formula that I have supplied? I hope this explanation is clear.

May 8th, 2002, 03:28 PM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	purely for this example.... try this in your other sheet, cell A1 : =INDIRECT("'Instrument Tags'!"&CHAR(SUBSTITUTE(MOD(ROW(),7),0,7)+83)&ROUNDUP((ROW()/7),0)+2) and just copy down as far as is needed If anyone can follow what I've done here, I'd appreciate it if you could suggest an easier or tidier way : the logic of transposing coupled with then dropping down a row after each 7th record was a bit of a mind-twister.... hence the ugly formula but it works...so this should get you started _________________ Hope this helps, Chris be careful to ensure the single quotes around ....instrument tags.... it's sometimes easy to miss them on this board [ This Message was edited by: Chris Davison on 2002-05-08 14:29 ] just to test fully : this formula in A1 copied down to row 3486, produces the follwoing result in cell 3486 : "Z500" which is the 7th column (column Z) on row 500 from your Instruments Tags sheet [ This Message was edited by: Chris Davison on 2002-05-08 14:44 ]

May 8th, 2002, 03:37 PM	#9
rdeters New Member Join Date: May 2002 Location: Calgary, Canada Posts: 40	Chris, you are the man. Thank you ever so much. I have no idea how you figured this out, but that is pretty good! Thanks again.

May 8th, 2002, 04:01 PM	#10
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	no worries... I'll run through it if you want, step by step I suspect you will follow it given your own input of that mathmatical formula you suggested.... give me a shout and I'll post the individual parts, although it may not be today as it's 11pm here and to be honest this single post has exhausted me !