How do I make a column of data sort into rows?

[Guest]

I have a column of Titles sorted alphabetically from position A2 down to
A293. I would like to put it on another worksheet so that it is covering 3
columns yet still be alphabetical, i.e.; A2=alpha, B2=bravo, C2=charlie,
A3=delta, B3=echo, C3=fox-trot, A4=golf, etc. I have tried sorting left to
right, but it does nothing. Can someone please help?

 

[Bernard Liengme]

Look in http://www.ozgrid.com/VBA/MiscVBA.htm and go down to Print a Long
Column
best wishes

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Sheets("Sheet3").Range("a2:c99").Value = _
ArrayReshape(Sheets("Sheet2").Range("a2:a293"), 98, 3)

Alan Beban

 

[Gord Dibben]

Cara

Enter this in B1 =INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:B)-1)

Copy across to D1.

Copy B11 down untill you get 0's showing up.

When happy, copy columns B and paste special>values>ok>esc.

Delete column A

Can you live with a macro? Run this one and enter 3 in the InputBox

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 GoTo endit
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:
MsgBox "You Have Cancelled " & Chr(13) _
& "Or Not Entered Criteria" & Chr(13) _
& "Try Again"

End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[T. Valko]

Try this:

=INDEX($A$2:$A$293,ROW(A1)*3-3+COLUMN(A1))

Copy across to 3 columns then down until you get #REF! errors.

Then convert the formulas to constants:

Select the range of formulas
Goto the menu Edit>Copy
Then: Edit>Paste Special>Values>OK

Delete any #REF! errors.

Then you can delete the original list if desired.

Biff

 

[Guest]

Thank you all. I will attempt what you have suggested tomorrow when I am
more awake. I will let you know how it worked. I am afraid this will be a
bit over my head. I unfortunately do not know a lot about these types of
macros and formulas. I only know some of the basics of Excel, apparently. I
thought I knew more, but after reading some stuff on these boards I realize I
am practically illiterate when it comes to Excel. I will have to enroll in
some classes!