Data from one column set up into ten columns

[Tobi]

I have some data in one column:

1. ABC
2.
3. xyz
4.
5. 123
6. aaa
7.
8.
9.
10.
11. DEF
12.
13. rst
14.
15. 456
16. bbb
17.
18.
19.
20.
21. GHI
....
and so on...

I'd like to set this data in one row but in ten columns, like this:

---A--- ---B--- ---C--- ---D--- ---E--- ---F--- ---G--- ---H--- ...
1. ABC xyz 123 aaa
....

How it can be done?

 

[JulieD]

Hi Tobi

copy it,
click in A2, choose edit / paste special -values
then delete row 1

Cheers
JulieD

 

[Tobi]

Hi Tobi

copy it,
click in A2, choose edit / paste special -values
then delete row 1

Oh come on... I've got about 2.000 rows there. I need solution which
automatically move all this data into columns.

 

[JulieD]

Hi Tobi

sorry, crystal ball is not working tonight .. .there's no indication at all
in your original post that you had more than one column or that you were
looking for a macro solution .

and i'm not really sure how you're going to break the 2000 rows down (as
you've only got 256 columns), do you want every 10 rows of column A into
into 1 row of 10 columns wide or what?

Provide a bit more information and we might be able to come up with a
workable solution.

Regards
JulieD

 

[Gord Dibben]

Tobi

VBA macro.

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 = 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
End Sub

Gord Dibben Excel MVP

 

[Tobi]

Tobi

VBA macro.

[cut]

Big thanks!