Sorting down and over

[JICDB]

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?

 

[xlm]

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]

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.

 

[Bill Sharpe]

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

 

[Gord Dibben]

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("C11").Value = Range("A1:B1").Value
Range("E1:F1").Value = Range("A1:B1").Value
Range("A1").Select
fileerror:
End Sub


Gord Dibben MS Excel MVP

 

[Billy Liddel]

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]

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.

 

[JICDB]

Thanks for your macro help. I'm not sure I have the skills to use but I'm
going to try. Thanks so much,