Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Transposing list of numbers

I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.
--
jlhcat
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default Transposing list of numbers

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim J As Long

Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = 7 'InputBox("Enter Number of Columns Desired")
For i = 1 To rng.Row Step nocols
Cells(J, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
J = J + 1
Next
Range(Cells(J, "A"), Cells(rng.Row, "A")).ClearContents

End Sub


Gord Dibben MS Excel MVP

On Wed, 6 Feb 2008 09:47:06 -0800, jlhcat
wrote:

I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 5,651
Default Transposing list of numbers

On Wed, 6 Feb 2008 09:47:06 -0800, jlhcat
wrote:

I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like
to transpose it across 7 columns and 58 rows. Is there an easy way to do
this? I understand I can do the copy/paste special/transpose for 7 at a
time. Thank you.


B1: =INDEX($A:$A,COLUMNS($A:A)+(ROWS($1:1)-1)*7,1)

Fill right to Column H, then select B1:H1 and fill down to row 60 or so.

If your data does not start in A1 -- for example, if it starts in A3, then
merely change the array argument as appropriate:

=INDEX($A$3:$A$500,COLUMNS($A:A)+(ROWS($1:1)-1)*7,1)

The other arguments remain unchanged.
--ron
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
Transposing Deena Excel Worksheet Functions 2 October 27th 06 05:13 PM
change a vertical list of numbers to horizontal list from 1 cell caz Excel Discussion (Misc queries) 3 September 27th 06 12:11 PM
Concatenating and transposing a row of numbers Colin Hayes Excel Worksheet Functions 14 July 15th 06 08:54 PM
how to extract unique numbers once from a list of repeated numbers? [email protected] Excel Discussion (Misc queries) 2 May 2nd 06 04:17 PM
transposing an array converts dates into numbers Mehmet Excel Worksheet Functions 3 April 21st 06 07:04 AM


All times are GMT +1. The time now is 04:25 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"