Dave,
Thanks for reply. Yes, the 2 colums start of as shown in first example and
then end up like the second example with spaces on each side incremented down
as the numbers get larger in both the colums. If the number is the same it
will end up on the same row.
WOW, tried it. it works great. I now need to expand this to do this in the
2 colums but shift the data in the right 3 colums of each sorted row as well.
See example
123 data data data
125 data data data
156data data data
256 data data data 256 data data data
359 data data data
365 data data data
458 data data data
598 data data data
875 data data data 875 data data data
890 data data data
923 data data data
950 data data data
Will this work as well or are major modification neccessary?
Bob
"Dave Peterson" wrote:
Everything ends up in two columns, though, right?
Add a header to row 1 and try this macro that I've saved this from a few
previous posts:
Option Explicit
Sub testme()
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long
Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
'change the mycols to the number of columns that
'are associated with column B
myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If
If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With
Application.ScreenUpdating = True
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
blk&wht wrote:
If someone could help me with this that would be great if it's even posible.
I have 2 colums as shown below:
123 156
125 256
256 359
365 598
458 875
875 890
923 950
I would like to sort and increment the numbers down in order as shown below:
123
125
156
256 256
359
365
458
598
875 875
890
923
950
Like numbers would be on the same line, different number would be
numerically incremented in order in the colums they are in.
Thanks for help in advance
Bob
Email:
--
Dave Peterson