Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
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: |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
So A:D are grouped together and E:H are grouped together?
Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("a2").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("e2").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 1 has headers! Set Col1st = .Range(.Cells(2, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(2, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 2 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub blk&wht wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
Dave,
Thank you for the sorting tip. I even have it sorting in the columns I want and the number of columns associated with the sort but I can't get it to work for the first 4 columns. I have a 4 3 4 column sheet. The numbers are located in the 1st column and the 8th column. Everything in columns 2-4 and 9-11 (which works with the "myCols") shifts down. I can't get columns 2-4 to shift with the result of column 1. Please see sample. That's what the sheet look like. What do need to do to make columns 2,3 and 4 to shift down as well? Thanks, Bob Cola Colh 614-53700-001 aa aa aa 614-53700-001 aa aa aa 615-20402-034 aa aa aa 615-20402-034 aa aa aa 615-20402-068 aa aa aa 615-20402-068 aa aa aa 615-20402-073 aa aa aa 615-20402-073 aa aa aa 615-20402-085 aa aa aa 615-20402-085 aa aa aa 615-20402-099 aa aa aa 615-20402-099 aa aa aa 615-20403-001 aa aa aa 615-20403-001 aa aa aa 615-20403-047 aa aa aa 615-20403-047 aa aa aa 615-20403-069 aa aa aa 615-20403-069 aa aa aa 615-20403-438 aa aa aa 615-20403-438 aa aa aa 615-20404-001 aa aa aa 615-20404-001 aa aa aa 615-20404-038 aa aa aa 615-20404-038 aa aa aa 615-20404-065 aa aa aa 615-20404-065 aa aa aa 615-20405-001 aa aa aa 615-20405-001 aa aa aa 615-20405-018 aa aa aa 615-20405-018 aa aa aa 615-20405-095 aa aa aa 615-20405-095 aa aa aa 615-20406-001 aa aa aa 615-20406-001 aa aa aa 615-20406-018 aa aa aa 615-20406-018 aa aa aa 615-20406-051 aa aa aa 615-20406-051 aa aa aa 615-20406-078 aa aa aa 615-20406-078 aa aa aa 615-60050-001 aa aa aa 615-20603-099 aa aa aa 621-62057-002 aa aa aa 621-62057-002 aa aa aa 624-10653-001 aa aa aa 624-10653-001 aa aa aa 628-54100-001 aa aa aa 628-54100-001 aa aa aa 630-10652-001 aa aa aa 630-10652-001 aa aa aa 630-10652-002 aa aa aa 630-10652-002 aa aa aa 630-10652-003 aa aa aa 630-10652-003 aa aa aa 630-52125-002 aa aa aa 630-53516-001 aa aa aa 632-10270-003 aa aa aa 632-10270-003 aa aa aa 632-10270-004 aa aa aa 632-10270-004 aa aa aa 632-10682-002 aa aa aa 632-10682-002 aa aa aa 634-10627-002 aa aa aa 634-10627-002 aa aa aa 637-10646-001 aa aa aa 637-10646-001 aa aa aa 637-10655-001 aa aa aa 637-10655-001 aa aa aa 640-10636-001 aa aa aa 640-10636-001 aa aa aa 640-10648-001 aa aa aa 640-10648-001 aa aa aa 640-10650-001 aa aa aa 640-10650-001 aa aa aa 640-10673-001 aa aa aa 640-10673-001 aa aa aa "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
Dave,
Sorry, we crossed paths. Having issues with MS Site. Yes that's it. It works. All I have to do is shift it to the columns where I need them. Thanks you so much, this will be a life saver. Now I don't have to do this manually anymore. If I could call on you for one more issue that I have I would appreciate it. I'll email you back after I have it explained. Thanks again for your help. Bob "Dave Peterson" wrote: So A:D are grouped together and E:H are grouped together? Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("a2").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("e2").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 1 has headers! Set Col1st = .Range(.Cells(2, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(2, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 2 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub blk&wht wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
If you use 11 columns (4 3 4), then what happens with those 3 columns in the
middle. Are they left alone? Do they travel with the first group of 4? Do they travel with the last group of 4? blk&wht wrote: Dave, Thank you for the sorting tip. I even have it sorting in the columns I want and the number of columns associated with the sort but I can't get it to work for the first 4 columns. I have a 4 3 4 column sheet. The numbers are located in the 1st column and the 8th column. Everything in columns 2-4 and 9-11 (which works with the "myCols") shifts down. I can't get columns 2-4 to shift with the result of column 1. Please see sample. That's what the sheet look like. What do need to do to make columns 2,3 and 4 to shift down as well? Thanks, Bob Cola Colh 614-53700-001 aa aa aa 614-53700-001 aa aa aa 615-20402-034 aa aa aa 615-20402-034 aa aa aa 615-20402-068 aa aa aa 615-20402-068 aa aa aa 615-20402-073 aa aa aa 615-20402-073 aa aa aa 615-20402-085 aa aa aa 615-20402-085 aa aa aa 615-20402-099 aa aa aa 615-20402-099 aa aa aa 615-20403-001 aa aa aa 615-20403-001 aa aa aa 615-20403-047 aa aa aa 615-20403-047 aa aa aa 615-20403-069 aa aa aa 615-20403-069 aa aa aa 615-20403-438 aa aa aa 615-20403-438 aa aa aa 615-20404-001 aa aa aa 615-20404-001 aa aa aa 615-20404-038 aa aa aa 615-20404-038 aa aa aa 615-20404-065 aa aa aa 615-20404-065 aa aa aa 615-20405-001 aa aa aa 615-20405-001 aa aa aa 615-20405-018 aa aa aa 615-20405-018 aa aa aa 615-20405-095 aa aa aa 615-20405-095 aa aa aa 615-20406-001 aa aa aa 615-20406-001 aa aa aa 615-20406-018 aa aa aa 615-20406-018 aa aa aa 615-20406-051 aa aa aa 615-20406-051 aa aa aa 615-20406-078 aa aa aa 615-20406-078 aa aa aa 615-60050-001 aa aa aa 615-20603-099 aa aa aa 621-62057-002 aa aa aa 621-62057-002 aa aa aa 624-10653-001 aa aa aa 624-10653-001 aa aa aa 628-54100-001 aa aa aa 628-54100-001 aa aa aa 630-10652-001 aa aa aa 630-10652-001 aa aa aa 630-10652-002 aa aa aa 630-10652-002 aa aa aa 630-10652-003 aa aa aa 630-10652-003 aa aa aa 630-52125-002 aa aa aa 630-53516-001 aa aa aa 632-10270-003 aa aa aa 632-10270-003 aa aa aa 632-10270-004 aa aa aa 632-10270-004 aa aa aa 632-10682-002 aa aa aa 632-10682-002 aa aa aa 634-10627-002 aa aa aa 634-10627-002 aa aa aa 637-10646-001 aa aa aa 637-10646-001 aa aa aa 637-10655-001 aa aa aa 637-10655-001 aa aa aa 640-10636-001 aa aa aa 640-10636-001 aa aa aa 640-10648-001 aa aa aa 640-10648-001 aa aa aa 640-10650-001 aa aa aa 640-10650-001 aa aa aa 640-10673-001 aa aa aa 640-10673-001 aa aa aa "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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
Dave, This works great, but......
I made a few changes. I want it to start at U6 for the first set and AB6 for the second set. I changed mykeycol1st to U6 and mykeycol2nd to AB6. Changed set Col1st .Range(.Cells(2 to a 6 and set Col2nd .Range(.cells(2 to a 6 as well. Changed iRow = 1 to iRow -= 6 so it will start at the correct row. It now does what it is supposed to do where it is supposed to to it. It's fast. I then changed the "sheet1" to my sheet name "bom". It works but it is very slow. It takes about 20 seconds or so. What did I do wrong. Below is the modified macro with your macro below that. Bob Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("u6").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("ab6").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 5 has headers! Set Col1st = .Range(.Cells(6, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(6, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 6 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub "Dave Peterson" wrote: So A:D are grouped together and E:H are grouped together? Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("a2").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("e2").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 1 has headers! Set Col1st = .Range(.Cells(2, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(2, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 2 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
If you turn calculation to manual, then run the macro, then turn calculation
back to automatic, is it quicker? If yes, then add a line of code to change calc to manual at the top and a line to change it back at the bottom. blk&wht wrote: Dave, This works great, but...... I made a few changes. I want it to start at U6 for the first set and AB6 for the second set. I changed mykeycol1st to U6 and mykeycol2nd to AB6. Changed set Col1st .Range(.Cells(2 to a 6 and set Col2nd .Range(.cells(2 to a 6 as well. Changed iRow = 1 to iRow -= 6 so it will start at the correct row. It now does what it is supposed to do where it is supposed to to it. It's fast. I then changed the "sheet1" to my sheet name "bom". It works but it is very slow. It takes about 20 seconds or so. What did I do wrong. Below is the modified macro with your macro below that. Bob Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("u6").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("ab6").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 5 has headers! Set Col1st = .Range(.Cells(6, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(6, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 6 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub "Dave Peterson" wrote: So A:D are grouped together and E:H are grouped together? Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("a2").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("e2").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 1 has headers! Set Col1st = .Range(.Cells(2, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(2, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 2 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
Dave,
Yes that was it. Thanks again. Still working on my other question. Bob "Dave Peterson" wrote: If you turn calculation to manual, then run the macro, then turn calculation back to automatic, is it quicker? If yes, then add a line of code to change calc to manual at the top and a line to change it back at the bottom. blk&wht wrote: Dave, This works great, but...... I made a few changes. I want it to start at U6 for the first set and AB6 for the second set. I changed mykeycol1st to U6 and mykeycol2nd to AB6. Changed set Col1st .Range(.Cells(2 to a 6 and set Col2nd .Range(.cells(2 to a 6 as well. Changed iRow = 1 to iRow -= 6 so it will start at the correct row. It now does what it is supposed to do where it is supposed to to it. It's fast. I then changed the "sheet1" to my sheet name "bom". It works but it is very slow. It takes about 20 seconds or so. What did I do wrong. Below is the modified macro with your macro below that. Bob Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("u6").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("ab6").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 5 has headers! Set Col1st = .Range(.Cells(6, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(6, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 6 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub "Dave Peterson" wrote: So A:D are grouped together and E:H are grouped together? Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("a2").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("e2").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 1 has headers! Set Col1st = .Range(.Cells(2, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(2, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 2 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting 2 colums of numbers and incremening them down
There are other things that can be toggled to make your macro work faster.
You may want to do something like: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work--all that other stuff goes here. 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub blk&wht wrote: Dave, Yes that was it. Thanks again. Still working on my other question. Bob "Dave Peterson" wrote: If you turn calculation to manual, then run the macro, then turn calculation back to automatic, is it quicker? If yes, then add a line of code to change calc to manual at the top and a line to change it back at the bottom. blk&wht wrote: Dave, This works great, but...... I made a few changes. I want it to start at U6 for the first set and AB6 for the second set. I changed mykeycol1st to U6 and mykeycol2nd to AB6. Changed set Col1st .Range(.Cells(2 to a 6 and set Col2nd .Range(.cells(2 to a 6 as well. Changed iRow = 1 to iRow -= 6 so it will start at the correct row. It now does what it is supposed to do where it is supposed to to it. It's fast. I then changed the "sheet1" to my sheet name "bom". It works but it is very slow. It takes about 20 seconds or so. What did I do wrong. Below is the modified macro with your macro below that. Bob Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("u6").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("ab6").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 5 has headers! Set Col1st = .Range(.Cells(6, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(6, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 6 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub "Dave Peterson" wrote: So A:D are grouped together and E:H are grouped together? Option Explicit Sub testme() Application.ScreenUpdating = False Dim wks As Worksheet Dim Col1st As Range Dim Col2nd As Range Dim iRow As Long Dim myCols1st As Long Dim myCols2nd As Long Dim myKeyCol1st As Long Dim myKeyCol2nd As Long Set wks = Worksheets("sheet1") wks.DisplayPageBreaks = False With wks myKeyCol1st = .Range("a2").Column myCols1st = 4 ' 4 columns associated with the 1st key myKeyCol2nd = .Range("e2").Column myCols2nd = 4 '4 columns associated with the 2nd key 'row 1 has headers! Set Col1st = .Range(.Cells(2, myKeyCol1st), _ .Cells(.Rows.Count, myKeyCol1st).End(xlUp)) Set Col2nd = .Range(.Cells(2, myKeyCol2nd), _ .Cells(.Rows.Count, myKeyCol2nd).End(xlUp)) With Col1st.Resize(, myCols1st) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With With Col2nd.Resize(, myCols2nd) .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo End With iRow = 2 Do If IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ And IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then Exit Do End If If .Cells(iRow, myKeyCol1st).Value _ = .Cells(iRow, myKeyCol2nd).Value _ Or IsEmpty(.Cells(iRow, myKeyCol1st).Value) _ Or IsEmpty(.Cells(iRow, myKeyCol2nd).Value) Then 'do nothing Else If .Cells(iRow, myKeyCol1st).Value _ .Cells(iRow, myKeyCol2nd).Value Then .Cells(iRow, myKeyCol1st).Resize(1, myCols1st).Insert _ shift:=xlDown Else .Cells(iRow, myKeyCol2nd).Resize(1, myCols2nd).Insert _ shift:=xlDown End If End If iRow = iRow + 1 Loop End With Application.ScreenUpdating = True End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|