Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting more than 3 columns
Sorry for what undoubtedly a dumb question. I have a large spreadsheet that
I'm trying to create a macro that will sort the data (12,000+ rows) using a sort with 4 keys. The following code gets me 3 columns - but will not support the addition of a "Key4". When I do it manually it's a two stage process - first, sort on the entire spreadsheet on the first 3 columns - next stage is to then manually page through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4 whenever I see the value of column 1 has changed. I'm thinking I'll need to sort on the column 1 - then loop through the spreadsheet identifying to identify the first row in which a new value appears in column one and continue on to identify the last row in which that same value appears. Then execute a sort on a range defined by the first / last row identified. Unfortunately, I'm brand new to this VB stuff and simply don't know how to do this. Can anybody point me in the right direction? SpaceNorman ******** Sub NewSort() Range("SortRange").Select Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _ , Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _ :=xlSortNormal Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting more than 3 columns
On Mon, 7 Apr 2008 14:04:01 -0700, Space Norman
wrote: Sorry for what undoubtedly a dumb question. I have a large spreadsheet that I'm trying to create a macro that will sort the data (12,000+ rows) using a sort with 4 keys. The following code gets me 3 columns - but will not support the addition of a "Key4". When I do it manually it's a two stage process - first, sort on the entire spreadsheet on the first 3 columns - next stage is to then manually page through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4 whenever I see the value of column 1 has changed. I'm thinking I'll need to sort on the column 1 - then loop through the spreadsheet identifying to identify the first row in which a new value appears in column one and continue on to identify the last row in which that same value appears. Then execute a sort on a range defined by the first / last row identified. Unfortunately, I'm brand new to this VB stuff and simply don't know how to do this. Can anybody point me in the right direction? SpaceNorman ******** Sub NewSort() Range("SortRange").Select Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _ , Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _ :=xlSortNormal Range("A1").Select End Sub Try this: First sort on the least significant column. Then sort on the other three columns. The result will be what you expect - I guess. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting more than 3 columns
Yes, it's too bad we're limited to three columns, but usually that's
sufficient. What I do is create a "Sorter" column concatenating all four key columns into it: (Col-E) = CONCATENATE(A1,B1,C1,D1) Make sure key columns are in the correct order. Copy down column "E" (or whatever), and sort on the Sorter column. Hide it if you want. "Space Norman" wrote: Sorry for what undoubtedly a dumb question. I have a large spreadsheet that I'm trying to create a macro that will sort the data (12,000+ rows) using a sort with 4 keys. The following code gets me 3 columns - but will not support the addition of a "Key4". When I do it manually it's a two stage process - first, sort on the entire spreadsheet on the first 3 columns - next stage is to then manually page through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4 whenever I see the value of column 1 has changed. I'm thinking I'll need to sort on the column 1 - then loop through the spreadsheet identifying to identify the first row in which a new value appears in column one and continue on to identify the last row in which that same value appears. Then execute a sort on a range defined by the first / last row identified. Unfortunately, I'm brand new to this VB stuff and simply don't know how to do this. Can anybody point me in the right direction? SpaceNorman ******** Sub NewSort() Range("SortRange").Select Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _ , Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _ :=xlSortNormal Range("A1").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting more than 3 columns
I am not sure what version of Excel you are using, but this seemed to work
for me using Excel 2007. This was a short sort range of 12 rows, but you should be able to change the ending range to encompass what you are looking for. Sub sorttest() ' ' sorttest Macro ' ' Columns("A:E").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("A2:A12") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("B2:B12") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C2:C12") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("D2:D12") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("E2:E12") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:E12") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub "Space Norman" wrote: Sorry for what undoubtedly a dumb question. I have a large spreadsheet that I'm trying to create a macro that will sort the data (12,000+ rows) using a sort with 4 keys. The following code gets me 3 columns - but will not support the addition of a "Key4". When I do it manually it's a two stage process - first, sort on the entire spreadsheet on the first 3 columns - next stage is to then manually page through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4 whenever I see the value of column 1 has changed. I'm thinking I'll need to sort on the column 1 - then loop through the spreadsheet identifying to identify the first row in which a new value appears in column one and continue on to identify the last row in which that same value appears. Then execute a sort on a range defined by the first / last row identified. Unfortunately, I'm brand new to this VB stuff and simply don't know how to do this. Can anybody point me in the right direction? SpaceNorman ******** Sub NewSort() Range("SortRange").Select Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _ , Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _ :=xlSortNormal Range("A1").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting more than 3 columns
On Apr 7, 11:04 pm, Space Norman
wrote: Sorry for what undoubtedly a dumb question. I have a large spreadsheet that I'm trying to create a macro that will sort the data (12,000+ rows) using a sort with 4 keys. The following code gets me 3 columns - but will not support the addition of a "Key4". When I do it manually it's a two stage process - first, sort on the entire spreadsheet on the first 3 columns - next stage is to then manually page through the spreadsheet and performing a "mini-sort" on columns 2, 3 and 4 whenever I see the value of column 1 has changed. I'm thinking I'll need to sort on the column 1 - then loop through the spreadsheet identifying to identify the first row in which a new value appears in column one and continue on to identify the last row in which that same value appears. Then execute a sort on a range defined by the first / last row identified. Unfortunately, I'm brand new to this VB stuff and simply don't know how to do this. Can anybody point me in the right direction? SpaceNorman ******** Sub NewSort() Range("SortRange").Select Selection.Sort Key1:=Range("W5"), Order1:=xlAscending, Key2:=Range("X5") _ , Order2:=xlAscending, Key3:=Range("Y5"), Order3:=xlAscending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _ :=xlSortNormal Range("A1").Select End Sub declare SortRange |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
freezing columns labels while sorting columns | New Users to Excel | |||
sorting columns | New Users to Excel | |||
Sorting by Two Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
Regarding Sorting Columns | Excel Discussion (Misc queries) |