Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a column of data sort into rows?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a column of data sort into rows?
Look in http://www.ozgrid.com/VBA/MiscVBA.htm and go down to Print a Long
Column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Cara" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a column of data sort into rows?
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 Cara wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a column of data sort into rows?
Cara
Enter this in B1 =INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:B)-1) Copy across to D1. Copy B1:D1 down untill you get 0's showing up. When happy, copy columns B:D and paste specialvaluesokesc. 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 InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Wed, 24 Jan 2007 12:38:51 -0800, Cara wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a column of data sort into rows?
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 EditCopy Then: EditPaste SpecialValuesOK Delete any #REF! errors. Then you can delete the original list if desired. Biff "Cara" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a column of data sort into rows?
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! "Cara" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
macro | Excel Discussion (Misc queries) | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
How do I format a column in alpha order? | New Users to Excel |