Transpose

[Joe]

Hi

I have 3394 number verticaly from A1 to A3394. I would like to have these
numbers horizontally say in groups of 16( B1:B16, C1:C16, etc). I know
transpose can do this put this will have to be in some sort of macro so that
I do not have to Copy an Transpose every group of numbers.

Is there anyway of doing this.

Thanks in adance

JK

 

[Leo Heuser]

Hi Joe

It's not clear to me, what you mean.
Using transpose in groups of 16 would
place group 1 (A1:A16) in B1:Q1, group 2
(A17:A32) in B2:Q2, etc.??

 

[Frank Kabel]

Hi
in B1 enter the formula
=OFFSET($A$1,ROW()-1+(COLUMN()-2)*16,0)
and copy down/to the right. Afterwards copy the data range and insert
it again with 'Edit - Paste Special - Values' to remove the formulas

 

[Jazzer]

Hi,

The following macro does the trick. It assumes that your numbers star
from A1. Newly arranged numbers start from B2:Q2, B3:Q3, ... Afte
that, you can remove the column A.

This macro is made with XL2003, so there may be some arguments i
PasteSpecial-operation that gives an error message in earlie
Excel-versions. Normally in that case, you can just delete th
argument.


Sub TransposeInGroups()
Dim lRow As Long

lRow = 1

With Application.ActiveSheet

While .Cells(lRow, 1) <> ""

.Range(.Cells(lRow, 1), .Cells(lRow + 15, 1)).Copy

.Range(.Cells(Int(lRow / 16) + 1, 2), .Cells(Int(lRow / 16) + 1
2)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone
SkipBlanks:=False, Transpose:=True

lRow = lRow + 16

Wend

End With

End Sub

- Asse

 

[Joe]

I'm sorry your right. But I think you know what I mean.

 

[Gord Dibben]

Joe

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 GoTo endit
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
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
Exit Sub
endit:
End Sub

Copy/paste the code to a general module in your workbook. Hit ALT + F11
to get to the Visual Basic Editor. View>Project Explorer. Left-click on your
workbook/project. Insert>Module. Paste in here.

ALT + F11 to go back to Excel. Tools>Macro>Macros. Select the macro by name
then Run.

NOTE: When asked "how many columns" enter 16.

Remember also. There is no "Undo" from a macro. Make sure you try this on a
copy of the worksheet first.

Gord Dibben Excel MVP

 

[Leo Heuser]

I'm sorry your right. But I think you know what I mean.

My guess would have been "transpose", but over the years many
of my guesses have been wrong, so I have stopped guessing
and instead asked the poster for a clarification (also a hint to
proof read your question before posting it)

When there are two contradicting possibilities to choose from
(transpose or putting the values in B1:B16, C1:C16 etc) and either
seems to be a meaningful choice, I'm not going to use time on
finding a solution for one of them, only to be told, that it was the
other one, you wanted

LeoH