sorting by Contract #'s
Hello,
I am needing help from the excel Masters. I am completely lost and have tried to find my way for the past 3 days. I have given up. Please help me. If I sort from Column A to column H this macro works great Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("A12"), Order1:=xlAscending, _ Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Now I need to reverse this whole thing and place the contract #'s in column H instead of A and the above macro doesn't work. What do I need to do. Next, I need to sort all of this info by date or by contract #. So, I need a macro to ask me what contract number to be first so that my program will graph it. ie: 123 354 456 Now I want Contract 456 on top. 456 123 354 But now I want Contract 354 on top. 354 123 456 I am sorry to make this so difficult. Hope some on can help me. My email is Eric Dickson |
sorting by Contract #'s
I don't think you're going to get excel to sort numbers using your custom sort
criteria. Sorting numbers is gonna be ascending or descending. Maybe you could add another field that returns a number for each of the contracts so you could sort by that field. Eric wrote: Hello, I am needing help from the excel Masters. I am completely lost and have tried to find my way for the past 3 days. I have given up. Please help me. If I sort from Column A to column H this macro works great Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("A12"), Order1:=xlAscending, _ Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Now I need to reverse this whole thing and place the contract #'s in column H instead of A and the above macro doesn't work. What do I need to do. Next, I need to sort all of this info by date or by contract #. So, I need a macro to ask me what contract number to be first so that my program will graph it. ie: 123 354 456 Now I want Contract 456 on top. 456 123 354 But now I want Contract 354 on top. 354 123 456 I am sorry to make this so difficult. Hope some on can help me. My email is Eric Dickson -- Dave Peterson |
sorting by Contract #'s
Dave,
What if I use a list box and ask for a certain contract #. Also, What about the first part of my problem? Eric "Dave Peterson" wrote: I don't think you're going to get excel to sort numbers using your custom sort criteria. Sorting numbers is gonna be ascending or descending. Maybe you could add another field that returns a number for each of the contracts so you could sort by that field. Eric wrote: Hello, I am needing help from the excel Masters. I am completely lost and have tried to find my way for the past 3 days. I have given up. Please help me. If I sort from Column A to column H this macro works great Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("A12"), Order1:=xlAscending, _ Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Now I need to reverse this whole thing and place the contract #'s in column H instead of A and the above macro doesn't work. What do I need to do. Next, I need to sort all of this info by date or by contract #. So, I need a macro to ask me what contract number to be first so that my program will graph it. ie: 123 354 456 Now I want Contract 456 on top. 456 123 354 But now I want Contract 354 on top. 354 123 456 I am sorry to make this so difficult. Hope some on can help me. My email is Eric Dickson -- Dave Peterson |
sorting by Contract #'s
I don't think you're ever going to be able to sort numbers into the order you
want using plain old excel's dialogs. Maybe a helper column. Maybe changing the data. Maybe something like: Option Explicit Sub testme() Dim wks As Worksheet Dim RngToSort As Range Dim LastRow As Long Dim StartCell As Range Set wks = ActiveSheet With wks Set StartCell = .Range("H11") LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row Set RngToSort = .Range("A11:H" & LastRow) With RngToSort .Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With End With End Sub I figure you know if the data has headers. Change xlyes to xlno if there are no headers--but why take a chance that excel will guess incorrectly. I used column H to find the last used row--is that ok? And I don't how many columns are in the range to be sorted. I use A:H. And do you still want to sort by the second column in the range (as the secondary key)? Eric wrote: Dave, What if I use a list box and ask for a certain contract #. Also, What about the first part of my problem? Eric "Dave Peterson" wrote: I don't think you're going to get excel to sort numbers using your custom sort criteria. Sorting numbers is gonna be ascending or descending. Maybe you could add another field that returns a number for each of the contracts so you could sort by that field. Eric wrote: Hello, I am needing help from the excel Masters. I am completely lost and have tried to find my way for the past 3 days. I have given up. Please help me. If I sort from Column A to column H this macro works great Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("A12"), Order1:=xlAscending, _ Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Now I need to reverse this whole thing and place the contract #'s in column H instead of A and the above macro doesn't work. What do I need to do. Next, I need to sort all of this info by date or by contract #. So, I need a macro to ask me what contract number to be first so that my program will graph it. ie: 123 354 456 Now I want Contract 456 on top. 456 123 354 But now I want Contract 354 on top. 354 123 456 I am sorry to make this so difficult. Hope some on can help me. My email is Eric Dickson -- Dave Peterson -- Dave Peterson |
sorting by Contract #'s
Dave,
Thank you so much for your help I will be trying this on Friday morning. You asked if the second column was going to be the second key and the answer is yes. In sorting the data by contract number you said that I may need to use a helper column, what exactly do you mean? What kind of information would be in that helper column. It can't be a number correct, because if it were we would be back to the same problem as before sorting in ascending or decending order. So what should that column be? Each contract will have a slight variasion of each other so how will know what to put in that helper column or will I have to go in an manually enter something? IE: Contract # Helper Column (town) 456 manchester 234 Windsor 123 Mystic Would I know sort according to Town? If I do this will each time I enter 456 will Manchester automaticlly be put in to the Town column? ERic "Dave Peterson" wrote: I don't think you're ever going to be able to sort numbers into the order you want using plain old excel's dialogs. Maybe a helper column. Maybe changing the data. Maybe something like: Option Explicit Sub testme() Dim wks As Worksheet Dim RngToSort As Range Dim LastRow As Long Dim StartCell As Range Set wks = ActiveSheet With wks Set StartCell = .Range("H11") LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row Set RngToSort = .Range("A11:H" & LastRow) With RngToSort .Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With End With End Sub I figure you know if the data has headers. Change xlyes to xlno if there are no headers--but why take a chance that excel will guess incorrectly. I used column H to find the last used row--is that ok? And I don't how many columns are in the range to be sorted. I use A:H. And do you still want to sort by the second column in the range (as the secondary key)? Eric wrote: Dave, What if I use a list box and ask for a certain contract #. Also, What about the first part of my problem? Eric "Dave Peterson" wrote: I don't think you're going to get excel to sort numbers using your custom sort criteria. Sorting numbers is gonna be ascending or descending. Maybe you could add another field that returns a number for each of the contracts so you could sort by that field. Eric wrote: Hello, I am needing help from the excel Masters. I am completely lost and have tried to find my way for the past 3 days. I have given up. Please help me. If I sort from Column A to column H this macro works great Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("A12"), Order1:=xlAscending, _ Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Now I need to reverse this whole thing and place the contract #'s in column H instead of A and the above macro doesn't work. What do I need to do. Next, I need to sort all of this info by date or by contract #. So, I need a macro to ask me what contract number to be first so that my program will graph it. ie: 123 354 456 Now I want Contract 456 on top. 456 123 354 But now I want Contract 354 on top. 354 123 456 I am sorry to make this so difficult. Hope some on can help me. My email is Eric Dickson -- Dave Peterson -- Dave Peterson |
sorting by Contract #'s
First, when I use the word Sort, I want an alpha-numeric sort. I don't
understand why you'd want to sort a field to put a certain value on the top and then sort again to put another value at the top of the sorted list. You could define a custom list (tools|Options|custom lists) and excel will sort your data according to that list. But if you want to rearrange values, you'll have to delete that list and recreate a different custom list. My thought was that you could create a table on another sheet (say sheet2) and build a table that defines how you want your data sorted. Contract Sortorder1 Sortorder2 sortorder3 sortorder4 1234 4 1 1 4 1235 1 2 3 3 1236 2 4 4 1 1237 3 3 2 2 Then you could use a helper column and put a formula like: =vlookup(a2,sheet2!a:e,2,false) and copy down. Then sort your data using that column. If you needed to sort in a different order, you could change the =vlookup() formula to return a different column. Or you could use lots of helper columns and return every conceivable order you want and then sort your data by whatever column you want. But I'm still confused. Eric wrote: Dave, Thank you so much for your help I will be trying this on Friday morning. You asked if the second column was going to be the second key and the answer is yes. In sorting the data by contract number you said that I may need to use a helper column, what exactly do you mean? What kind of information would be in that helper column. It can't be a number correct, because if it were we would be back to the same problem as before sorting in ascending or decending order. So what should that column be? Each contract will have a slight variasion of each other so how will know what to put in that helper column or will I have to go in an manually enter something? IE: Contract # Helper Column (town) 456 manchester 234 Windsor 123 Mystic Would I know sort according to Town? If I do this will each time I enter 456 will Manchester automaticlly be put in to the Town column? ERic "Dave Peterson" wrote: I don't think you're ever going to be able to sort numbers into the order you want using plain old excel's dialogs. Maybe a helper column. Maybe changing the data. Maybe something like: Option Explicit Sub testme() Dim wks As Worksheet Dim RngToSort As Range Dim LastRow As Long Dim StartCell As Range Set wks = ActiveSheet With wks Set StartCell = .Range("H11") LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row Set RngToSort = .Range("A11:H" & LastRow) With RngToSort .Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With End With End Sub I figure you know if the data has headers. Change xlyes to xlno if there are no headers--but why take a chance that excel will guess incorrectly. I used column H to find the last used row--is that ok? And I don't how many columns are in the range to be sorted. I use A:H. And do you still want to sort by the second column in the range (as the secondary key)? Eric wrote: Dave, What if I use a list box and ask for a certain contract #. Also, What about the first part of my problem? Eric "Dave Peterson" wrote: I don't think you're going to get excel to sort numbers using your custom sort criteria. Sorting numbers is gonna be ascending or descending. Maybe you could add another field that returns a number for each of the contracts so you could sort by that field. Eric wrote: Hello, I am needing help from the excel Masters. I am completely lost and have tried to find my way for the past 3 days. I have given up. Please help me. If I sort from Column A to column H this macro works great Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("A12"), Order1:=xlAscending, _ Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Now I need to reverse this whole thing and place the contract #'s in column H instead of A and the above macro doesn't work. What do I need to do. Next, I need to sort all of this info by date or by contract #. So, I need a macro to ask me what contract number to be first so that my program will graph it. ie: 123 354 456 Now I want Contract 456 on top. 456 123 354 But now I want Contract 354 on top. 354 123 456 I am sorry to make this so difficult. Hope some on can help me. My email is Eric Dickson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
sorting by Contract #'s
Dave,
I hate to have you confussed. Your probably thinking that I'm some kind of wack job so, let me try to explain what I'm doing and trying to accomplish. First I am in Quality Control where I design and test Asphalt (roadways). I am making a spreadsheet that I can imput all the information from each test right into this data base I have created. We test material each day and for different contracts (we do alot of state work). We may do 4 or 5 tests per day and apply it to as many as 5 state contracts. We chart individual test results and moving average of these properties. At the end of a job we must supply charts of each test to the state authorities. The data base places everything in cronilogical order by date. Our charts range from January to December and we may have 50 different contracts with 200 or more tests involved in the charts. I want to have the accessablitliy to chart out the properties by date (which I can do now) or by a particular contract so that I can give them to state authorities at the end of the job This is how it looks now. The charts for Gmm will be according to date (7-7 through 7-9) and this chart will represent the material for 3 days and 3 different contracts. contract # Date Gmm Gse Gsa etc..... 1. 123 7/7 2.569 2.897 2.987 2. 698 7/8 2.587 2.901 2.999 3. 123 7/8 2.569 2.994 2.999 4. 456 7/9 " " " 5. 123 7/9 I now want it to be like the following: I finished Contract 123 so I need to print its charts with out the other contracts so..... contract # Date Gmm Gse Gsa etc..... 1. 123 7/7 2.569 2.897 2.987 2. 123 7/8 2.569 2.994 2.999 3. 123 7/9 4. 698 7/8 2.587 2.901 2.999 5. 456 7/9 " " " My spreadsheet only prints from #1 first. This is why I need to manipulate the contract #'s. I hope this makes more sense. Please give me another chance. I am desperate to get this thing working. Eric "Dave Peterson" wrote: First, when I use the word Sort, I want an alpha-numeric sort. I don't understand why you'd want to sort a field to put a certain value on the top and then sort again to put another value at the top of the sorted list. You could define a custom list (tools|Options|custom lists) and excel will sort your data according to that list. But if you want to rearrange values, you'll have to delete that list and recreate a different custom list. My thought was that you could create a table on another sheet (say sheet2) and build a table that defines how you want your data sorted. Contract Sortorder1 Sortorder2 sortorder3 sortorder4 1234 4 1 1 4 1235 1 2 3 3 1236 2 4 4 1 1237 3 3 2 2 Then you could use a helper column and put a formula like: =vlookup(a2,sheet2!a:e,2,false) and copy down. Then sort your data using that column. If you needed to sort in a different order, you could change the =vlookup() formula to return a different column. Or you could use lots of helper columns and return every conceivable order you want and then sort your data by whatever column you want. But I'm still confused. Eric wrote: Dave, Thank you so much for your help I will be trying this on Friday morning. You asked if the second column was going to be the second key and the answer is yes. In sorting the data by contract number you said that I may need to use a helper column, what exactly do you mean? What kind of information would be in that helper column. It can't be a number correct, because if it were we would be back to the same problem as before sorting in ascending or decending order. So what should that column be? Each contract will have a slight variasion of each other so how will know what to put in that helper column or will I have to go in an manually enter something? IE: Contract # Helper Column (town) 456 manchester 234 Windsor 123 Mystic Would I know sort according to Town? If I do this will each time I enter 456 will Manchester automaticlly be put in to the Town column? ERic "Dave Peterson" wrote: I don't think you're ever going to be able to sort numbers into the order you want using plain old excel's dialogs. Maybe a helper column. Maybe changing the data. Maybe something like: Option Explicit Sub testme() Dim wks As Worksheet Dim RngToSort As Range Dim LastRow As Long Dim StartCell As Range Set wks = ActiveSheet With wks Set StartCell = .Range("H11") LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row Set RngToSort = .Range("A11:H" & LastRow) With RngToSort .Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With End With End Sub I figure you know if the data has headers. Change xlyes to xlno if there are no headers--but why take a chance that excel will guess incorrectly. I used column H to find the last used row--is that ok? And I don't how many columns are in the range to be sorted. I use A:H. And do you still want to sort by the second column in the range (as the secondary key)? Eric wrote: Dave, What if I use a list box and ask for a certain contract #. Also, What about the first part of my problem? Eric "Dave Peterson" wrote: I don't think you're going to get excel to sort numbers using your custom sort criteria. Sorting numbers is gonna be ascending or descending. Maybe you could add another field that returns a number for each of the contracts so you could sort by that field. Eric wrote: Hello, I am needing help from the excel Masters. I am completely lost and have tried to find my way for the past 3 days. I have given up. Please help me. If I sort from Column A to column H this macro works great Dim sheet1 As Worksheet Dim myrange As Range Dim lrow As Long Set sheet1 = ActiveSheet Set myrange = sheet1.Range("A11") lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6) myrange.sort key1:=Range("A12"), Order1:=xlAscending, _ Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlSortNormal Now I need to reverse this whole thing and place the contract #'s in column H instead of A and the above macro doesn't work. What do I need to do. Next, I need to sort all of this info by date or by contract #. So, I need a macro to ask me what contract number to be first so that my program will graph it. ie: 123 354 456 Now I want Contract 456 on top. 456 123 354 But now I want Contract 354 on top. 354 123 456 I am sorry to make this so difficult. Hope some on can help me. My email is Eric Dickson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
sorting by Contract #'s
I'm still confused.
It looks to me like you're sorting by contract number, then by date. If it's the printing of the data that's a problem, maybe you could determine the unique contract numbers, then apply data|filter|autofilter to that column. Then filter through each unique value and print those visible rows. I still don't understand, but maybe this will help... Maybe... Option Explicit Sub testme() Dim RngToSort As Range Dim LastRow As Long Dim StartCell As Range Dim myUniqueRng As Range Dim myCell As Range Dim wks As Worksheet Dim ContractCol As Long Set wks = Worksheets("sheet1") With wks 'remove any existing autofilter .AutoFilterMode = False Set StartCell = .Range("H11") LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row ContractCol = StartCell.Column Set RngToSort = .Range("A11:H" & LastRow) With RngToSort .Cells.Sort Key1:=.Columns(ContractCol), Order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With 'advanced filter by column H (8th column in A:H) 'to get the unique list RngToSort.Columns(ContractCol).AdvancedFilter _ action:=xlFilterInPlace, unique:=True If RngToSort.Columns(ContractCol).Cells _ .SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then MsgBox "nothing in column A after the headers!" Exit Sub End If With RngToSort.Columns(ContractCol) 'avoid the headers and just take the visible cells Set myUniqueRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) End With For Each myCell In myUniqueRng.Cells RngToSort.Columns(ContractCol).AutoFilter _ field:=1, Criteria1:=myCell.Value .PrintOut preview:=True Next myCell 'remove the autofilter from the worksheet .AutoFilterMode = False End With End Sub Earlier the contract column was column H. But in your example, it migrated back to column A. That makes it more confusing (to me at least). Eric wrote: Dave, I hate to have you confussed. Your probably thinking that I'm some kind of wack job so, let me try to explain what I'm doing and trying to accomplish. First I am in Quality Control where I design and test Asphalt (roadways). I am making a spreadsheet that I can imput all the information from each test right into this data base I have created. We test material each day and for different contracts (we do alot of state work). We may do 4 or 5 tests per day and apply it to as many as 5 state contracts. We chart individual test results and moving average of these properties. At the end of a job we must supply charts of each test to the state authorities. The data base places everything in cronilogical order by date. Our charts range from January to December and we may have 50 different contracts with 200 or more tests involved in the charts. I want to have the accessablitliy to chart out the properties by date (which I can do now) or by a particular contract so that I can give them to state authorities at the end of the job This is how it looks now. The charts for Gmm will be according to date (7-7 through 7-9) and this chart will represent the material for 3 days and 3 different contracts. contract # Date Gmm Gse Gsa etc..... 1. 123 7/7 2.569 2.897 2.987 2. 698 7/8 2.587 2.901 2.999 3. 123 7/8 2.569 2.994 2.999 4. 456 7/9 " " " 5. 123 7/9 I now want it to be like the following: I finished Contract 123 so I need to print its charts with out the other contracts so..... contract # Date Gmm Gse Gsa etc..... 1. 123 7/7 2.569 2.897 2.987 2. 123 7/8 2.569 2.994 2.999 3. 123 7/9 4. 698 7/8 2.587 2.901 2.999 5. 456 7/9 " " " My spreadsheet only prints from #1 first. This is why I need to manipulate the contract #'s. I hope this makes more sense. Please give me another chance. I am desperate to get this thing working. Eric <<snipped -- Dave Peterson |
All times are GMT +1. The time now is 11:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com