Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing | Excel Worksheet Functions | |||
change a vertical list of numbers to horizontal list from 1 cell | Excel Discussion (Misc queries) | |||
Concatenating and transposing a row of numbers | Excel Worksheet Functions | |||
how to extract unique numbers once from a list of repeated numbers? | Excel Discussion (Misc queries) | |||
transposing an array converts dates into numbers | Excel Worksheet Functions |