How can I transpose data from a spreadsheet into a form that does.
 

How can I transpose data from a spreadsheet where the data is presented in
contiguous columns to another sheet where the data needs to be presented in
non contiguous rows? For example conisder a table column heading "Number"
data 1,2,3,4 filling down. This has to be presented on another sheet as,
"Number:" 1, several blanks accross, "Number: 2", several blanks accross,
"Number: 3" etc. I have to take the data from a spread sheet and present it
in a form. Is there a function to do this so I don't have to type it all out
again?

Thanks

One way

In Sheet1
-------------
In A1 down is the source col:

Number
1
2
3
etc

In Sheet2
-------------
Assume you want it to be extracted from Sheet1 and placed as:

In A1: Number: 1
<skip 2 cols
In D1: Number: 2
<skip 2 cols
In G1:Number: 3
etc

Put in A1:
=IF(MOD(COLUMN(A1),3)<1,"",Sheet1!$A$1&":
"&OFFSET(Sheet1!$A$1,INT((COLUMN(A1)+2)/3),))
Copy across

--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Joby" wrote in message
...
How can I transpose data from a spreadsheet where the data is presented in
contiguous columns to another sheet where the data needs to be presented
in
non contiguous rows? For example conisder a table column heading "Number"
data 1,2,3,4 filling down. This has to be presented on another sheet as,
"Number:" 1, several blanks accross, "Number: 2", several blanks accross,
"Number: 3" etc. I have to take the data from a spread sheet and present
it
in a form. Is there a function to do this so I don't have to type it all
out
again?

Thanks

Thanks, although I think my explanation was misinterpreted what I ment was in
sheet 1 you have in column A1

1
2
3

and in sheet 2 you want:

Cell A1=Cell A1 Sheet 1

Cell A4=Cell A2 Sheet 1

Cell A7=Cell A3 Sheet 1

The blank cells inbetween have left alone because there is other and labels
in those.

Thanks

Joby

"Max" wrote:

One way

In Sheet1
-------------
In A1 down is the source col:

Number
1
2
3
etc

In Sheet2
-------------
Assume you want it to be extracted from Sheet1 and placed as:

In A1: Number: 1
<skip 2 cols
In D1: Number: 2
<skip 2 cols
In G1:Number: 3
etc

Put in A1:
=IF(MOD(COLUMN(A1),3)<1,"",Sheet1!$A$1&":
"&OFFSET(Sheet1!$A$1,INT((COLUMN(A1)+2)/3),))
Copy across

--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Joby" wrote in message
...
How can I transpose data from a spreadsheet where the data is presented in
contiguous columns to another sheet where the data needs to be presented
in
non contiguous rows? For example conisder a table column heading "Number"
data 1,2,3,4 filling down. This has to be presented on another sheet as,
"Number:" 1, several blanks accross, "Number: 2", several blanks accross,
"Number: 3" etc. I have to take the data from a spread sheet and present
it
in a form. Is there a function to do this so I don't have to type it all
out
again?

Thanks

One way ..

In Sheet1
-------------
in A1 down, you have

1
2
3
etc

In Sheet2
------------
You have some existing values / labels in A2, A3, A5, A6, etc
and you want to put the values from Sheet1's col A into A1, A4, A7, etc

Use an empty col, say, col E

Put in E1:
=IF(MOD(ROW(A1),3)=1,OFFSET(Sheet1!$A$1,(ROW(A1)+2 )/3-1,),IF(A1="","",A1))
Copy down

Copy col E and
Right-click on A1 paste special values ok
to overwrite col A

This should give what you're after ..

--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Joby" wrote in message
...
Thanks, although I think my explanation was misinterpreted what I ment was
in
sheet 1 you have in column A1

1
2
3

and in sheet 2 you want:

Cell A1=Cell A1 Sheet 1

Cell A4=Cell A2 Sheet 1

Cell A7=Cell A3 Sheet 1

The blank cells inbetween have left alone because there is other and
labels
in those.

Thanks

Joby