Transpose Problem

  • Thread starter Thread starter saturnin02
  • Start date Start date
S

saturnin02

Hi,
XL 2002
I have a matrix as such

1997 1998 1999 .....
A
B
C

The values in the cells are vlook up values

I need the results of above in ONE column, as such:

A1997
B1997
C1997
A1998
B1998
C1998
A1999
B1999
C1999

How can I get this quickly and in a "automated" way--by which I mean with a
formula as opposed to copy/paste, etc.

Tx a million.

S
 
How many columns and how many rows - Be precise

Is it to be on the same sheet or another sheet?
 
Ken, 12 Rows. 100 Columns
Can be on same sheet or other sheet--doesn't really matter.
S
 
Assuming your data looks like this

1997 1998 1999 .....
A Val1 Val2 Val3
B Val4 Val5 Val6
C Val7 Val8 Val9

A1997
B1997
C1997
A1998
B1998
C1998
A1999
B1999
C1999

Are you looking for

A B C
A 1997 Val1
B 1997 Val4
C 1997 Val7
A 1998 Val2
B 1998 Val5
C 1998 Val8
A 1999 Val3
B 1999 Val6
C 1999 Val9

as if so, then simply put the following into your sheet, given that your current
data resides in A1:CW13 assuming 12 rows of data plus headers in row 1 and 100
columns of data plus headers in Col A

Copy A2:A13 and paste into cell A15 (Will fill A15:A25)
In cells B15:B25 put 1997 in each cell

In cell A26 put =A15
In cell B26 put =B15+1

Copy A26:B26, select A27:A1114 and paste

Now in cell C15 put the following and paste down

=INDEX($A$1:$CW$12,MATCH($A15,$A$1:$A$12,0),MATCH(B$15,$A$1:$CW$1,0))

This will then use the values in Cols A & B to look up the relevant value in
your table
 
Hence my confusion, and assumptions about your layout.

If I ignore the headers and assume you simply have a 12x100 array in A1:L100 and
you would like that to appear in a single column, effectively giving you your
100 columns one on top of the other, then in say cell A15 put the following and
copy down as many cells as required to house all the elements from your array:-

=OFFSET($A$1,MOD(ROW()-15,12),FLOOR((ROW()-15)/12,1))

Note, the hardwired 15 represents the fact that the first formula is going in on
row 15, so if you put it in cell A16 to START, then change it to 16 and so on.
The hardwired 12 represents the 12 rows of data, so if you only had 10 rows of
data you would use 10 in there.

If it was a one off operation, then the following link to an article on John
Walkenbach's site would give you this data in an easy couple of steps via the
use of a Pivot table and it's 'drill to details' option:-

http://j-walk.com/ss/excel/usertips/tip068.htm
 
Ken,
I think that your latest post is more what I am looking for.
I appreciate your helpful suggestions.
Let me actually use it in this spreadsheet of mine and come back to you on
it by posting again in this thread shortly.
Tx,
S
 
Well Ken, It works great and is much simpler (with all due respect to your
peer).
What about if I want to have instead of each column follow another in the
one target column, I want the rows from top to bottom to write to the target
column, in that order (row by row)?
What modifications need I make?

As is now:
1 2 3
A
B
C

Instead of getting the following (which your formula gives me):

A1
B1
C1
A2
B2
C2

Have

A1
A2
A3
B1
B2
B3
etc.

For the icing on tha cake....
Tx,
S
 
You just have to flip the second and third argument around and adjust for the
right number of rows/columns, eg for 100 columns assuming you start this in Row
15:-

=OFFSET($A$1,FLOOR((ROW()-15)/100,1),MOD(ROW()-15,100))

Don't forget, Chip's stuff is a lot more generic than what I have just given
you, and uses another function anyway. Given the same parameters, were he to
hardwire in those parameters as i have done here, those formulas would reduce a
lot. I've given you explanations in text, whereas a lot of the explanation in
Chip's formulas is through the formula variables themselves.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



saturnin02 said:
Well Ken, It works great and is much simpler (with all due respect to your
peer).
What about if I want to have instead of each column follow another in the
one target column, I want the rows from top to bottom to write to the target
column, in that order (row by row)?
What modifications need I make?

As is now:
1 2 3
A
B
C

Instead of getting the following (which your formula gives me):

A1
B1
C1
A2
B2
C2

Have

A1
A2
A3
B1
B2
B3
etc.

For the icing on tha cake....
Tx,
S
Click to expand...
<snip>
 
Got it.
Tx a lot, Ken.
Very appreciated.
S
Ken Wright said:
You just have to flip the second and third argument around and adjust for the
right number of rows/columns, eg for 100 columns assuming you start this in Row
15:-

=OFFSET($A$1,FLOOR((ROW()-15)/100,1),MOD(ROW()-15,100))

Don't forget, Chip's stuff is a lot more generic than what I have just given
you, and uses another function anyway. Given the same parameters, were he to
hardwire in those parameters as i have done here, those formulas would reduce a
lot. I've given you explanations in text, whereas a lot of the explanation in
Chip's formulas is through the formula variables themselves.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :-)
-------------------------------------------------------------------------- --




<snip>
Click to expand...
 
You're welcome :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



saturnin02 said:
Got it.
Tx a lot, Ken.
Very appreciated.
Click to expand...
<snip>
 
Back
Top