Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
I'm hoping someone can save me alot of work and aggrevation. I have a list
of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
I don't get how your data is being layout now.
Would you show an example how your current layout is like and what is the result you like to have. -- If this posting was helpful, please click on the Yes button Thank You cheers, "JICDB" wrote: I'm hoping someone can save me alot of work and aggrevation. I have a list of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
My data looks like this:
Addison 111-222-333-444 Aurora 541 - 542 - 543 Bensenville 444-555-666 20 pages worth of this data What I want (but don't think I can do with Excel): Addison 111-222-333-444 Calumet City 222-111 Aurora 541 - 542 - 543 Crystal Lake 222 Bensenville 444-555-666 Danville 444 continues in alpha order but wraps to top continued in alpha order of page on right - will be 10 pages or so. "xlm" wrote: I don't get how your data is being layout now. Would you show an example how your current layout is like and what is the result you like to have. -- If this posting was helpful, please click on the Yes button Thank You cheers, "JICDB" wrote: I'm hoping someone can save me alot of work and aggrevation. I have a list of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
JICDB wrote:
I'm hoping someone can save me alot of work and aggrevation. I have a list of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? If this is a one-shot deal, cutting and pasting should take care of it. If the list changes frequently you might be able to record a macro to handle the job. Bill |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
I will assume you have a title in A1 of City and a title in B1 of Route.
Run this macro to sort then move your data into 6 columns in a snaked fashion. Public Sub Snake2to6_sorted() Dim myRange As Range Dim colsize As Long Dim maxrow As Long Const numgroup As Integer = 3 Const NumCols As Integer = 6 On Error GoTo fileerror Columns("A:B").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal colsize = Int((ActiveSheet.UsedRange.Rows.Count + _ ((NumCols - 1)) / NumCols)) / numgroup Range("A2").Select With ActiveCell.Parent.UsedRange maxrow = .Cells(.Cells.Count).Row + 1 End With ActiveCell.Parent.Cells(maxrow, ActiveCell.Column) _ .End(xlUp).Offset(1, 0).Select Set myRange = Range(ActiveCell.Address & ":" _ & ActiveCell.Offset(-colsize, (numgroup - 2)).Address) myRange.Cut Destination:=ActiveSheet.Range("A2").Offset(0, _ ((NumCols) - (numgroup - 1))) Range("A2").Select Cells.End(xlDown).Offset(1, 0).Select Set NextRange = Range(ActiveCell.Address & ":" _ & ActiveCell.Offset(-colsize, (numgroup - 2)).Address) NextRange.Cut Destination:=ActiveSheet.Range("A2").Offset(0, _ (NumCols / numgroup)) Application.CutCopyMode = False Range("C1:D1").Value = Range("A1:B1").Value Range("E1:F1").Value = Range("A1:B1").Value Range("A1").Select fileerror: End Sub Gord Dibben MS Excel MVP On Tue, 23 Dec 2008 10:46:02 -0800, JICDB wrote: My data looks like this: Addison 111-222-333-444 Aurora 541 - 542 - 543 Bensenville 444-555-666 20 pages worth of this data What I want (but don't think I can do with Excel): Addison 111-222-333-444 Calumet City 222-111 Aurora 541 - 542 - 543 Crystal Lake 222 Bensenville 444-555-666 Danville 444 continues in alpha order but wraps to top continued in alpha order of page on right - will be 10 pages or so. "xlm" wrote: I don't get how your data is being layout now. Would you show an example how your current layout is like and what is the result you like to have. -- If this posting was helpful, please click on the Yes button Thank You cheers, "JICDB" wrote: I'm hoping someone can save me alot of work and aggrevation. I have a list of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
You can try this macro. It splits up the data into four parts and copies it
into adjacent columns so make sure that it does not overwrite any data. You can change the number of parts to split the data when you are prompted at the start. The original list is left in place so you can check the results. Option Explicit Sub Splitto4() Dim LNrows As Long Dim iSplitRows As Integer Dim iDestRows As Integer Dim iCols As Integer Dim iDestCol As Integer Dim N2Split As Variant Dim x() As Variant Dim iIndex As Integer Dim lStartRow As Long N2Split = InputBox("Enter the number of Lists To Copy", _ "Split Data", 4, 100, 100) LNrows = Range("A1").CurrentRegion.Rows.Count iSplitRows = LNrows \ N2Split + 1 iDestRows = iSplitRows lStartRow = 2 iCols = 2 iDestCol = iCols + 2 For iIndex = 1 To 4 x = Range(Cells(lStartRow, 1), Cells(iSplitRows, 2)) Range(Cells(2, iDestCol), Cells(iDestRows, iDestCol + 1)) = x iDestCol = iDestCol + 2 lStartRow = iSplitRows + 1 iSplitRows = iSplitRows + LNrows \ N2Split + 1 Next iIndex End Sub If you have not used a macro before, Press ALT + F11, Choose Insert, Module then copy the code into the module. Close the VB Editor then with the sheet containg the data Press ALT + F8, Click the macro name and click Run. Regards Peter Atherton "JICDB" wrote: I'm hoping someone can save me alot of work and aggrevation. I have a list of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
I'll try the code to see how it works but I'm not really good with VBA so I
might just copy and paste it for now. When I get better at VBA I will revisit this issue and see if I can get it to work. Thanks so much for your help. "Billy Liddel" wrote: You can try this macro. It splits up the data into four parts and copies it into adjacent columns so make sure that it does not overwrite any data. You can change the number of parts to split the data when you are prompted at the start. The original list is left in place so you can check the results. Option Explicit Sub Splitto4() Dim LNrows As Long Dim iSplitRows As Integer Dim iDestRows As Integer Dim iCols As Integer Dim iDestCol As Integer Dim N2Split As Variant Dim x() As Variant Dim iIndex As Integer Dim lStartRow As Long N2Split = InputBox("Enter the number of Lists To Copy", _ "Split Data", 4, 100, 100) LNrows = Range("A1").CurrentRegion.Rows.Count iSplitRows = LNrows \ N2Split + 1 iDestRows = iSplitRows lStartRow = 2 iCols = 2 iDestCol = iCols + 2 For iIndex = 1 To 4 x = Range(Cells(lStartRow, 1), Cells(iSplitRows, 2)) Range(Cells(2, iDestCol), Cells(iDestRows, iDestCol + 1)) = x iDestCol = iDestCol + 2 lStartRow = iSplitRows + 1 iSplitRows = iSplitRows + LNrows \ N2Split + 1 Next iIndex End Sub If you have not used a macro before, Press ALT + F11, Choose Insert, Module then copy the code into the module. Close the VB Editor then with the sheet containg the data Press ALT + F8, Click the macro name and click Run. Regards Peter Atherton "JICDB" wrote: I'm hoping someone can save me alot of work and aggrevation. I have a list of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting down and over
Thanks for your macro help. I'm not sure I have the skills to use but I'm
going to try. Thanks so much, "Gord Dibben" wrote: I will assume you have a title in A1 of City and a title in B1 of Route. Run this macro to sort then move your data into 6 columns in a snaked fashion. Public Sub Snake2to6_sorted() Dim myRange As Range Dim colsize As Long Dim maxrow As Long Const numgroup As Integer = 3 Const NumCols As Integer = 6 On Error GoTo fileerror Columns("A:B").Select Selection.Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal colsize = Int((ActiveSheet.UsedRange.Rows.Count + _ ((NumCols - 1)) / NumCols)) / numgroup Range("A2").Select With ActiveCell.Parent.UsedRange maxrow = .Cells(.Cells.Count).Row + 1 End With ActiveCell.Parent.Cells(maxrow, ActiveCell.Column) _ .End(xlUp).Offset(1, 0).Select Set myRange = Range(ActiveCell.Address & ":" _ & ActiveCell.Offset(-colsize, (numgroup - 2)).Address) myRange.Cut Destination:=ActiveSheet.Range("A2").Offset(0, _ ((NumCols) - (numgroup - 1))) Range("A2").Select Cells.End(xlDown).Offset(1, 0).Select Set NextRange = Range(ActiveCell.Address & ":" _ & ActiveCell.Offset(-colsize, (numgroup - 2)).Address) NextRange.Cut Destination:=ActiveSheet.Range("A2").Offset(0, _ (NumCols / numgroup)) Application.CutCopyMode = False Range("C1:D1").Value = Range("A1:B1").Value Range("E1:F1").Value = Range("A1:B1").Value Range("A1").Select fileerror: End Sub Gord Dibben MS Excel MVP On Tue, 23 Dec 2008 10:46:02 -0800, JICDB wrote: My data looks like this: Addison 111-222-333-444 Aurora 541 - 542 - 543 Bensenville 444-555-666 20 pages worth of this data What I want (but don't think I can do with Excel): Addison 111-222-333-444 Calumet City 222-111 Aurora 541 - 542 - 543 Crystal Lake 222 Bensenville 444-555-666 Danville 444 continues in alpha order but wraps to top continued in alpha order of page on right - will be 10 pages or so. "xlm" wrote: I don't get how your data is being layout now. Would you show an example how your current layout is like and what is the result you like to have. -- If this posting was helpful, please click on the Yes button Thank You cheers, "JICDB" wrote: I'm hoping someone can save me alot of work and aggrevation. I have a list of cities in alphabetical order in column A and in column B a list of the bus route numbers that serve those cities. This list is 20 pages long. If I show it the way it is there is plenty of white space with lots of trees being killed in the process. I want the data to show in two general columns but It must remain in alphabetical order by city. Sort of like Word when you put the data in columns. I want to use Excel because the remainder of the booklet is in Excel. Other than cutting and pasting the data at the page breaks and moving the data around manually is there some way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Sorting | Excel Worksheet Functions | |||
Sorting | Excel Discussion (Misc queries) | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) |