Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Joby
 
Posts: n/a
Default 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
  #2   Report Post  
Max
 
Posts: n/a
Default

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



  #3   Report Post  
Joby
 
Posts: n/a
Default

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




  #4   Report Post  
Max
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
copying data from Excel spreadsheet to another DavidB Excel Discussion (Misc queries) 1 January 11th 05 12:26 AM
Importing Data From Another Spreadsheet Tiziano Excel Discussion (Misc queries) 6 January 7th 05 03:35 AM
Entering data on template and then data going to a spreadsheet. KJH Excel Discussion (Misc queries) 3 December 24th 04 02:04 AM
Data Filter - Not all rows in spreadsheet will display in Autofilt Excel Help Excel Worksheet Functions 1 November 17th 04 06:40 PM


All times are GMT +1. The time now is 11:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"