Transposing Data

[Reg]

I have a number of excel data files that contain up to
220 items of data listed in groups of ten in one column.
I want to rearrange this data so that the first group of
ten are transposed to a row of ten columns wide, the next
group of ten transposed to the next row of ten columns
wide, etc. This is to continue until the end of the
original data has been reached (each data file has a
different # of grouped entries. I have looked at the
TRANSPOSE function and it appears to require manual
attention for each group of ten in each data set. I would
prefer some kind of formula or macro that would look
after the whole data set once initiated. I am trying to
enter formula but so far no luck.

 

[Frank Kabel]

Hi Reg
if your data is in column A on sheet 1 enter the following formula in
cell A1 on sheet2
=OFFSET('sheet1'!$A$1,COLUMN()-1+(ROW()-1)*10,0)
copy this to the right and down

 

[Gord Dibben]

Reg

If the data is consistently 10 sets, run this macro and enter 10 in the
inputbox.


Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = 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 Excel MVP