Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
I have a current Macro performing a sort for me ...
Range("A1:U700").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range _ ("J2"), Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal .... but now I need to insert another sort. Only 3 criteria are allowed. What I want to do is specify after the sort above that if column A is the letter d then the section will be sorted ascending by column I. I hope that makes sense. But Range for this new sort will be constantly changing it could be rows 229 through 234 or it could be rows 230 through 233 or any other possible combination. I don't know if this makes any sense but if anyone has any ideas I would love to hear them. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
What do you mean by "if column A is the letter d"? A column cannot have a
value, only a cell can have a value. Post back and describe what you have. An example would be good. HTH Otto "FrankM" wrote in message ... I have a current Macro performing a sort for me ... Range("A1:U700").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range _ ("J2"), Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ... but now I need to insert another sort. Only 3 criteria are allowed. What I want to do is specify after the sort above that if column A is the letter d then the section will be sorted ascending by column I. I hope that makes sense. But Range for this new sort will be constantly changing it could be rows 229 through 234 or it could be rows 230 through 233 or any other possible combination. I don't know if this makes any sense but if anyone has any ideas I would love to hear them. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
You are absolutely correct, a column can not have a value, I'm sorry for the
miscommunication. What I meant to say is if the cell in column A has the value, "d", then I want to sort those rows ascending by the values in the cells of column I. The number of cells in column A that have the value "d" will be changing throughout the day. But when I run the Macro I want it to select all the rows where the cell in the A column has the value "d" and sort it ascending by the values in column I. I already have a Macro performing the sorts listed previous this is just an additional sort after that one is done. I hope that make sense. "Otto Moehrbach" wrote: What do you mean by "if column A is the letter d"? A column cannot have a value, only a cell can have a value. Post back and describe what you have. An example would be good. HTH Otto "FrankM" wrote in message ... I have a current Macro performing a sort for me ... Range("A1:U700").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range _ ("J2"), Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ... but now I need to insert another sort. Only 3 criteria are allowed. What I want to do is specify after the sort above that if column A is the letter d then the section will be sorted ascending by column I. I hope that makes sense. But Range for this new sort will be constantly changing it could be rows 229 through 234 or it could be rows 230 through 233 or any other possible combination. I don't know if this makes any sense but if anyone has any ideas I would love to hear them. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
Any ideas? I'd appreciate any suggestions. Thank you.
"FrankM" wrote: You are absolutely correct, a column can not have a value, I'm sorry for the miscommunication. What I meant to say is if the cell in column A has the value, "d", then I want to sort those rows ascending by the values in the cells of column I. The number of cells in column A that have the value "d" will be changing throughout the day. But when I run the Macro I want it to select all the rows where the cell in the A column has the value "d" and sort it ascending by the values in column I. I already have a Macro performing the sorts listed previous this is just an additional sort after that one is done. I hope that make sense. "Otto Moehrbach" wrote: What do you mean by "if column A is the letter d"? A column cannot have a value, only a cell can have a value. Post back and describe what you have. An example would be good. HTH Otto "FrankM" wrote in message ... I have a current Macro performing a sort for me ... Range("A1:U700").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range _ ("J2"), Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ... but now I need to insert another sort. Only 3 criteria are allowed. What I want to do is specify after the sort above that if column A is the letter d then the section will be sorted ascending by column I. I hope that makes sense. But Range for this new sort will be constantly changing it could be rows 229 through 234 or it could be rows 230 through 233 or any other possible combination. I don't know if this makes any sense but if anyone has any ideas I would love to hear them. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
No ideas? Please, someone must have an idea.
"FrankM" wrote: Any ideas? I'd appreciate any suggestions. Thank you. "FrankM" wrote: You are absolutely correct, a column can not have a value, I'm sorry for the miscommunication. What I meant to say is if the cell in column A has the value, "d", then I want to sort those rows ascending by the values in the cells of column I. The number of cells in column A that have the value "d" will be changing throughout the day. But when I run the Macro I want it to select all the rows where the cell in the A column has the value "d" and sort it ascending by the values in column I. I already have a Macro performing the sorts listed previous this is just an additional sort after that one is done. I hope that make sense. "Otto Moehrbach" wrote: What do you mean by "if column A is the letter d"? A column cannot have a value, only a cell can have a value. Post back and describe what you have. An example would be good. HTH Otto "FrankM" wrote in message ... I have a current Macro performing a sort for me ... Range("A1:U700").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range _ ("J2"), Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ... but now I need to insert another sort. Only 3 criteria are allowed. What I want to do is specify after the sort above that if column A is the letter d then the section will be sorted ascending by column I. I hope that makes sense. But Range for this new sort will be constantly changing it could be rows 229 through 234 or it could be rows 230 through 233 or any other possible combination. I don't know if this makes any sense but if anyone has any ideas I would love to hear them. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
I think it's time to describe what you want again.
The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. FrankM wrote: No ideas? Please, someone must have an idea. "FrankM" wrote: Any ideas? I'd appreciate any suggestions. Thank you. "FrankM" wrote: You are absolutely correct, a column can not have a value, I'm sorry for the miscommunication. What I meant to say is if the cell in column A has the value, "d", then I want to sort those rows ascending by the values in the cells of column I. The number of cells in column A that have the value "d" will be changing throughout the day. But when I run the Macro I want it to select all the rows where the cell in the A column has the value "d" and sort it ascending by the values in column I. I already have a Macro performing the sorts listed previous this is just an additional sort after that one is done. I hope that make sense. "Otto Moehrbach" wrote: What do you mean by "if column A is the letter d"? A column cannot have a value, only a cell can have a value. Post back and describe what you have. An example would be good. HTH Otto "FrankM" wrote in message ... I have a current Macro performing a sort for me ... Range("A1:U700").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range _ ("J2"), Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ... but now I need to insert another sort. Only 3 criteria are allowed. What I want to do is specify after the sort above that if column A is the letter d then the section will be sorted ascending by column I. I hope that makes sense. But Range for this new sort will be constantly changing it could be rows 229 through 234 or it could be rows 230 through 233 or any other possible combination. I don't know if this makes any sense but if anyone has any ideas I would love to hear them. . -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
"Dave Peterson" wrote:
I think it's time to describe what you want again. The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. Thank you so much for your response. I will try to explain a little better what Im working on. I have a spreadsheet that currently has data in columns A to AC; it use to have data in just columns A to U. It looks like Im going to have to update my current Macro. The data is currently in rows 1 to 269 but everyday another couple of records are entered. Column A is an indication of the category. Everything starts out in Category €œa€ and then moves to category €œb€, €œc€, €œd€ and so forth. Different items move at different paces so one entry may move through each category faster than another entry. It is all part of the process. To keep each category together I would sort the spreadsheet on a regular basis. Then I decided it would be better to just create a Macro and click a button to do the sort for me. I then needed to have different categories sorted differently so I added to my sort the columns K and J. What I am now trying to do is enter an additional sort for the items in category €œd€, to be sorted ascending by the value in column I. The number of rows with €œd€ in the cell in column A will vary; sometimes there will be five of them sometimes there may be six or seven. The rows that this applies to will also change; €œd€ may be in column A rows 239, 240, 241 and 242 one day but rows 247, 248 and 249 the next. I want to come up with a way to select all the rows that have the value €œd€ in the cell of column A and sort that selection ascending by the value in column I. I understand the original Range may have been confusing. I think when I originally created this Macro I just inserted a Range larger than I thought I would need. It was fast and dirty but it accomplished what it needed to at the time. I would love to hear any thoughts or suggestions. Thank you. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
I'm gonna simplify the problem slightly.
Say you have data in A1:c10 that looks like this: a x $C$1 d y $C$2 d y $C$3 a z $C$4 a z $C$5 a z $C$6 d w $C$7 d w $C$8 d w $C$9 d w $C$10 Column B is the category indicator and column A is the sort direction indicator. You'd want row 1 (the only a in that "group") sorted ascending. You'd want rows 2-3 sorted descending you'd want rows 4-6 sorted ascending you'd want rows 7-10 sorted descending So column B serves as a "group" indicator as well as the sorting order direction. If that's kind of what you mean, then (just to verify)... What column contains the sort direction indicator? Is that code on every record in the group? What column contains the group indicator? Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? What are the columns you want sorted by? Which column uses the direction indicator? Are the other columns always sorted ascending (or descending)? FrankM wrote: "Dave Peterson" wrote: I think it's time to describe what you want again. The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. Thank you so much for your response. I will try to explain a little better what Im working on. I have a spreadsheet that currently has data in columns A to AC; it use to have data in just columns A to U. It looks like Im going to have to update my current Macro. The data is currently in rows 1 to 269 but everyday another couple of records are entered. Column A is an indication of the category. Everything starts out in Category €œa€ and then moves to category €œb€, €œc€, €œd€ and so forth. Different items move at different paces so one entry may move through each category faster than another entry. It is all part of the process. To keep each category together I would sort the spreadsheet on a regular basis. Then I decided it would be better to just create a Macro and click a button to do the sort for me. I then needed to have different categories sorted differently so I added to my sort the columns K and J. What I am now trying to do is enter an additional sort for the items in category €œd€, to be sorted ascending by the value in column I. The number of rows with €œd€ in the cell in column A will vary; sometimes there will be five of them sometimes there may be six or seven. The rows that this applies to will also change; €œd€ may be in column A rows 239, 240, 241 and 242 one day but rows 247, 248 and 249 the next. I want to come up with a way to select all the rows that have the value €œd€ in the cell of column A and sort that selection ascending by the value in column I. I understand the original Range may have been confusing. I think when I originally created this Macro I just inserted a Range larger than I thought I would need. It was fast and dirty but it accomplished what it needed to at the time. I would love to hear any thoughts or suggestions. Thank you. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
ps.
What is the top row of the first group? Can I use the category indicator column to find the last row of the data? Dave Peterson wrote: I'm gonna simplify the problem slightly. Say you have data in A1:c10 that looks like this: a x $C$1 d y $C$2 d y $C$3 a z $C$4 a z $C$5 a z $C$6 d w $C$7 d w $C$8 d w $C$9 d w $C$10 Column B is the category indicator and column A is the sort direction indicator. You'd want row 1 (the only a in that "group") sorted ascending. You'd want rows 2-3 sorted descending you'd want rows 4-6 sorted ascending you'd want rows 7-10 sorted descending So column B serves as a "group" indicator as well as the sorting order direction. If that's kind of what you mean, then (just to verify)... What column contains the sort direction indicator? Is that code on every record in the group? What column contains the group indicator? Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? What are the columns you want sorted by? Which column uses the direction indicator? Are the other columns always sorted ascending (or descending)? FrankM wrote: "Dave Peterson" wrote: I think it's time to describe what you want again. The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. Thank you so much for your response. I will try to explain a little better what Im working on. I have a spreadsheet that currently has data in columns A to AC; it use to have data in just columns A to U. It looks like Im going to have to update my current Macro. The data is currently in rows 1 to 269 but everyday another couple of records are entered. Column A is an indication of the category. Everything starts out in Category €œa€ and then moves to category €œb€, €œc€, €œd€ and so forth. Different items move at different paces so one entry may move through each category faster than another entry. It is all part of the process. To keep each category together I would sort the spreadsheet on a regular basis. Then I decided it would be better to just create a Macro and click a button to do the sort for me. I then needed to have different categories sorted differently so I added to my sort the columns K and J. What I am now trying to do is enter an additional sort for the items in category €œd€, to be sorted ascending by the value in column I. The number of rows with €œd€ in the cell in column A will vary; sometimes there will be five of them sometimes there may be six or seven. The rows that this applies to will also change; €œd€ may be in column A rows 239, 240, 241 and 242 one day but rows 247, 248 and 249 the next. I want to come up with a way to select all the rows that have the value €œd€ in the cell of column A and sort that selection ascending by the value in column I. I understand the original Range may have been confusing. I think when I originally created this Macro I just inserted a Range larger than I thought I would need. It was fast and dirty but it accomplished what it needed to at the time. I would love to hear any thoughts or suggestions. Thank you. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
Thank you so much for your assistance. I am sorry if it seems a bit confusing
what I'm trying to accomplish. Column A has the values in the cells, "a", "b", "c", "d" and so forth. The Macro I have to sort the information starts sorting this column and puts all the "a"s together, all the "b"s together and so forth. I then have data in columns B through AC (Names, IDs, Contact Information and other data). Columns I, J and K all have dates (I'm sorting the records base on these values). I hope that helps. I have done my best to answer the questions you asked. What column contains the sort direction indicator? I initial sort based on column A, this puts all the groups together. Then I have subsequent sorts on columns J and K Is that code on every record in the group? Yes What column contains the group indicator? Column A Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? Yes and that is part of the initial sort (by column A to put each group together) What are the columns you want sorted by? Which column uses the direction indicator? I would like to sort first by column A, this puts all the groups together. Then I want to sort by columns J and K. I would like to add an additional sort after these that selects any row that the value of the cell in column A is "d" and sort that selection by column I (ascending). Are the other columns always sorted ascending (or descending)? Yes What is the top row of the first group? The entries start at row 2, row 1 is a header row. I start my sorts based on row 2. Can I use the category indicator column to find the last row of the data? Yes. The last row will always have the value "a" in the cell in column A. "Dave Peterson" wrote: ps. What is the top row of the first group? Can I use the category indicator column to find the last row of the data? Dave Peterson wrote: I'm gonna simplify the problem slightly. Say you have data in A1:c10 that looks like this: a x $C$1 d y $C$2 d y $C$3 a z $C$4 a z $C$5 a z $C$6 d w $C$7 d w $C$8 d w $C$9 d w $C$10 Column B is the category indicator and column A is the sort direction indicator. You'd want row 1 (the only a in that "group") sorted ascending. You'd want rows 2-3 sorted descending you'd want rows 4-6 sorted ascending you'd want rows 7-10 sorted descending So column B serves as a "group" indicator as well as the sorting order direction. If that's kind of what you mean, then (just to verify)... What column contains the sort direction indicator? Is that code on every record in the group? What column contains the group indicator? Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? What are the columns you want sorted by? Which column uses the direction indicator? Are the other columns always sorted ascending (or descending)? FrankM wrote: "Dave Peterson" wrote: I think it's time to describe what you want again. The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. Thank you so much for your response. I will try to explain a little better what I€„¢m working on. I have a spreadsheet that currently has data in columns A to AC; it use to have data in just columns A to U. It looks like I€„¢m going to have to update my current Macro. The data is currently in rows 1 to 269 but everyday another couple of records are entered. Column A is an indication of the category. Everything starts out in Category €œa€ and then moves to category €œb€Â, €œc€Â, €œd€ and so forth. Different items move at different paces so one entry may move through each category faster than another entry. It is all part of the process. To keep each category together I would sort the spreadsheet on a regular basis. Then I decided it would be better to just create a Macro and click a button to do the sort for me. I then needed to have different categories sorted differently so I added to my sort the columns K and J. What I am now trying to do is enter an additional sort for the items in category €œd€Â, to be sorted ascending by the value in column I. The number of rows with €œd€ in the cell in column A will vary; sometimes there will be five of them sometimes there may be six or seven. The rows that this applies to will also change; €œd€ may be in column A rows 239, 240, 241 and 242 one day but rows 247, 248 and 249 the next. I want to come up with a way to select all the rows that have the value €œd€ in the cell of column A and sort that selection ascending by the value in column I. I understand the original Range may have been confusing. I think when I originally created this Macro I just inserted a Range larger than I thought I would need. It was fast and dirty but it accomplished what it needed to at the time. I would love to hear any thoughts or suggestions. Thank you. -- Dave Peterson -- Dave Peterson . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
xl2003 is limited to 3 sort fields. But you can sort as many times as you want.
It sounds like you just want to sort by column I first, then sort by columns A, J, K. So try recording a macro when you: Select the range to sort Sort by column I Then sort by A, J, K Then stop recording the macro. If this doesn't work, please explain what you want that's different from this. FrankM wrote: Thank you so much for your assistance. I am sorry if it seems a bit confusing what I'm trying to accomplish. Column A has the values in the cells, "a", "b", "c", "d" and so forth. The Macro I have to sort the information starts sorting this column and puts all the "a"s together, all the "b"s together and so forth. I then have data in columns B through AC (Names, IDs, Contact Information and other data). Columns I, J and K all have dates (I'm sorting the records base on these values). I hope that helps. I have done my best to answer the questions you asked. What column contains the sort direction indicator? I initial sort based on column A, this puts all the groups together. Then I have subsequent sorts on columns J and K Is that code on every record in the group? Yes What column contains the group indicator? Column A Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? Yes and that is part of the initial sort (by column A to put each group together) What are the columns you want sorted by? Which column uses the direction indicator? I would like to sort first by column A, this puts all the groups together. Then I want to sort by columns J and K. I would like to add an additional sort after these that selects any row that the value of the cell in column A is "d" and sort that selection by column I (ascending). Are the other columns always sorted ascending (or descending)? Yes What is the top row of the first group? The entries start at row 2, row 1 is a header row. I start my sorts based on row 2. Can I use the category indicator column to find the last row of the data? Yes. The last row will always have the value "a" in the cell in column A. "Dave Peterson" wrote: ps. What is the top row of the first group? Can I use the category indicator column to find the last row of the data? Dave Peterson wrote: I'm gonna simplify the problem slightly. Say you have data in A1:c10 that looks like this: a x $C$1 d y $C$2 d y $C$3 a z $C$4 a z $C$5 a z $C$6 d w $C$7 d w $C$8 d w $C$9 d w $C$10 Column B is the category indicator and column A is the sort direction indicator. You'd want row 1 (the only a in that "group") sorted ascending. You'd want rows 2-3 sorted descending you'd want rows 4-6 sorted ascending you'd want rows 7-10 sorted descending So column B serves as a "group" indicator as well as the sorting order direction. If that's kind of what you mean, then (just to verify)... What column contains the sort direction indicator? Is that code on every record in the group? What column contains the group indicator? Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? What are the columns you want sorted by? Which column uses the direction indicator? Are the other columns always sorted ascending (or descending)? FrankM wrote: "Dave Peterson" wrote: I think it's time to describe what you want again. The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. Thank you so much for your response. I will try to explain a little better what I€„¢m working on. I have a spreadsheet that currently has data in columns A to AC; it use to have data in just columns A to U. It looks like I€„¢m going to have to update my current Macro. The data is currently in rows 1 to 269 but everyday another couple of records are entered. Column A is an indication of the category. Everything starts out in Category €œa€ and then moves to category €œb€Â, €œc€Â, €œd€ and so forth. Different items move at different paces so one entry may move through each category faster than another entry. It is all part of the process. To keep each category together I would sort the spreadsheet on a regular basis. Then I decided it would be better to just create a Macro and click a button to do the sort for me. I then needed to have different categories sorted differently so I added to my sort the columns K and J. What I am now trying to do is enter an additional sort for the items in category €œd€Â, to be sorted ascending by the value in column I. The number of rows with €œd€ in the cell in column A will vary; sometimes there will be five of them sometimes there may be six or seven. The rows that this applies to will also change; €œd€ may be in column A rows 239, 240, 241 and 242 one day but rows 247, 248 and 249 the next. I want to come up with a way to select all the rows that have the value €œd€ in the cell of column A and sort that selection ascending by the value in column I. I understand the original Range may have been confusing. I think when I originally created this Macro I just inserted a Range larger than I thought I would need. It was fast and dirty but it accomplished what it needed to at the time. I would love to hear any thoughts or suggestions. Thank you. -- Dave Peterson -- Dave Peterson . -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
The sort I want done for column "I" is a range that will be changing so what
you propose won't work. the values in the cells in column "A" change from "a" to "b" to "c" to "d" to "e" and so forth. If the cell value in column "A" is "d" then I want all the rows with the value "d" in column "A" to be sorted by column "I". The catch is the number of rows with "d" in column "A" will be changing and their location will change too. Is there a way for a Macro to select all the rows which have the value "d" in the cell in column "A"? Then sort those rows by column "I"? "Dave Peterson" wrote: xl2003 is limited to 3 sort fields. But you can sort as many times as you want. It sounds like you just want to sort by column I first, then sort by columns A, J, K. So try recording a macro when you: Select the range to sort Sort by column I Then sort by A, J, K Then stop recording the macro. If this doesn't work, please explain what you want that's different from this. FrankM wrote: Thank you so much for your assistance. I am sorry if it seems a bit confusing what I'm trying to accomplish. Column A has the values in the cells, "a", "b", "c", "d" and so forth. The Macro I have to sort the information starts sorting this column and puts all the "a"s together, all the "b"s together and so forth. I then have data in columns B through AC (Names, IDs, Contact Information and other data). Columns I, J and K all have dates (I'm sorting the records base on these values). I hope that helps. I have done my best to answer the questions you asked. What column contains the sort direction indicator? I initial sort based on column A, this puts all the groups together. Then I have subsequent sorts on columns J and K Is that code on every record in the group? Yes What column contains the group indicator? Column A Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? Yes and that is part of the initial sort (by column A to put each group together) What are the columns you want sorted by? Which column uses the direction indicator? I would like to sort first by column A, this puts all the groups together. Then I want to sort by columns J and K. I would like to add an additional sort after these that selects any row that the value of the cell in column A is "d" and sort that selection by column I (ascending). Are the other columns always sorted ascending (or descending)? Yes What is the top row of the first group? The entries start at row 2, row 1 is a header row. I start my sorts based on row 2. Can I use the category indicator column to find the last row of the data? Yes. The last row will always have the value "a" in the cell in column A. "Dave Peterson" wrote: ps. What is the top row of the first group? Can I use the category indicator column to find the last row of the data? Dave Peterson wrote: I'm gonna simplify the problem slightly. Say you have data in A1:c10 that looks like this: a x $C$1 d y $C$2 d y $C$3 a z $C$4 a z $C$5 a z $C$6 d w $C$7 d w $C$8 d w $C$9 d w $C$10 Column B is the category indicator and column A is the sort direction indicator. You'd want row 1 (the only a in that "group") sorted ascending. You'd want rows 2-3 sorted descending you'd want rows 4-6 sorted ascending you'd want rows 7-10 sorted descending So column B serves as a "group" indicator as well as the sorting order direction. If that's kind of what you mean, then (just to verify)... What column contains the sort direction indicator? Is that code on every record in the group? What column contains the group indicator? Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? What are the columns you want sorted by? Which column uses the direction indicator? Are the other columns always sorted ascending (or descending)? FrankM wrote: "Dave Peterson" wrote: I think it's time to describe what you want again. The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. Thank you so much for your response. I will try to explain a little better what I€„¢m working on. I have a spreadsheet that currently has data in columns A to AC; it use to have data in just columns A to U. It looks like I€„¢m going to have to update my current Macro. The data is currently in rows 1 to 269 but everyday another couple of records are entered. Column A is an indication of the category. Everything starts out in Category €ŀœa€ and then moves to category €ŀœb€ÂÂ, €ŀœc€ÂÂ, €ŀœd€ and so forth. Different items move at different paces so one entry may move through each category faster than another entry. It is all part of the process. To keep each category together I would sort the spreadsheet on a regular basis. Then I decided it would be better to just create a Macro and click a button to do the sort for me. I then needed to have different categories sorted differently so I added to my sort the columns K and J. What I am now trying to do is enter an additional sort for the items in category €ŀœd€ÂÂ, to be sorted ascending by the value in column I. The number of rows with €ŀœd€ in the cell in column A will vary; sometimes there will be five of them sometimes there may be six or seven. The rows that this applies to will also change; €ŀœd€ may be in column A rows 239, 240, 241 and 242 one day but rows 247, 248 and 249 the next. I want to come up with a way to select all the rows that have the value €ŀœd€ in the cell of column A and sort that selection ascending by the value in column I. I understand the original Range may have been confusing. I think when I originally created this Macro I just inserted a Range larger than I thought I would need. It was fast and dirty but it accomplished what it needed to at the time. I would love to hear any thoughts or suggestions. Thank you. -- Dave Peterson -- Dave Peterson . -- Dave Peterson . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
I still don't understand why you can't sort the entire range by column I first.
Then do another sort against the entire range by column A, J, K (or whatever 3 keys are important). But yes, you can create a range that consists of the first cell in column A that has a D in it and the last cell in column A that has a D in it. This kind of thing would be run after you've sorted by column A. Option Explicit Sub testme() Dim wks As Worksheet Dim RngToSort As Range Dim TopCell As Range Dim BotCell As Range Dim myStr As String Set wks = Worksheets("Sheet1") myStr = "d" With wks With .Range("A:A") Set TopCell = .Cells.Find(what:=myStr, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If TopCell Is Nothing Then MsgBox "No " & myStr & " found in column A" Exit Sub End If Set BotCell = .Cells.Find(what:=myStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False) If BotCell Is Nothing Then 'this can't happen MsgBox "Major error!" Exit Sub End If If TopCell.Address = BotCell.Address Then MsgBox "Only one " & myStr & " found in column A" Exit Sub End If End With 'sort from column A to AC, right? Set RngToSort = .Range(TopCell, BotCell).Resize(, .Range("ac1").Column) With RngToSort .Sort key1:=.Columns(9), Order1:=xlAscending, _ key2:=.Columns(10), order2:=xlAscending, _ header:=xlNo End With End With End Sub FrankM wrote: The sort I want done for column "I" is a range that will be changing so what you propose won't work. the values in the cells in column "A" change from "a" to "b" to "c" to "d" to "e" and so forth. If the cell value in column "A" is "d" then I want all the rows with the value "d" in column "A" to be sorted by column "I". The catch is the number of rows with "d" in column "A" will be changing and their location will change too. Is there a way for a Macro to select all the rows which have the value "d" in the cell in column "A"? Then sort those rows by column "I"? "Dave Peterson" wrote: xl2003 is limited to 3 sort fields. But you can sort as many times as you want. It sounds like you just want to sort by column I first, then sort by columns A, J, K. So try recording a macro when you: Select the range to sort Sort by column I Then sort by A, J, K Then stop recording the macro. If this doesn't work, please explain what you want that's different from this. FrankM wrote: Thank you so much for your assistance. I am sorry if it seems a bit confusing what I'm trying to accomplish. Column A has the values in the cells, "a", "b", "c", "d" and so forth. The Macro I have to sort the information starts sorting this column and puts all the "a"s together, all the "b"s together and so forth. I then have data in columns B through AC (Names, IDs, Contact Information and other data). Columns I, J and K all have dates (I'm sorting the records base on these values). I hope that helps. I have done my best to answer the questions you asked. What column contains the sort direction indicator? I initial sort based on column A, this puts all the groups together. Then I have subsequent sorts on columns J and K Is that code on every record in the group? Yes What column contains the group indicator? Column A Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? Yes and that is part of the initial sort (by column A to put each group together) What are the columns you want sorted by? Which column uses the direction indicator? I would like to sort first by column A, this puts all the groups together. Then I want to sort by columns J and K. I would like to add an additional sort after these that selects any row that the value of the cell in column A is "d" and sort that selection by column I (ascending). Are the other columns always sorted ascending (or descending)? Yes What is the top row of the first group? The entries start at row 2, row 1 is a header row. I start my sorts based on row 2. Can I use the category indicator column to find the last row of the data? Yes. The last row will always have the value "a" in the cell in column A. "Dave Peterson" wrote: ps. What is the top row of the first group? Can I use the category indicator column to find the last row of the data? Dave Peterson wrote: I'm gonna simplify the problem slightly. Say you have data in A1:c10 that looks like this: a x $C$1 d y $C$2 d y $C$3 a z $C$4 a z $C$5 a z $C$6 d w $C$7 d w $C$8 d w $C$9 d w $C$10 Column B is the category indicator and column A is the sort direction indicator. You'd want row 1 (the only a in that "group") sorted ascending. You'd want rows 2-3 sorted descending you'd want rows 4-6 sorted ascending you'd want rows 7-10 sorted descending So column B serves as a "group" indicator as well as the sorting order direction. If that's kind of what you mean, then (just to verify)... What column contains the sort direction indicator? Is that code on every record in the group? What column contains the group indicator? Does your data need to be sorted by this indicator before it starts (so all category x's are together to start)???? What are the columns you want sorted by? Which column uses the direction indicator? Are the other columns always sorted ascending (or descending)? FrankM wrote: "Dave Peterson" wrote: I think it's time to describe what you want again. The code you posted sorted A1:U700. I'm not sure how looking at multiple cells in column A would determine what range to sort -- or even if it does. Thank you so much for your response. I will try to explain a little better what I€„¢m working on. I have a spreadsheet that currently has data in columns A to AC; it use to have data in just columns A to U. It looks like I€„¢m going to have to update my current Macro. The data is currently in rows 1 to 269 but everyday another couple of records are entered. Column A is an indication of the category. Everything starts out in Category €ŀœa€ and then moves to category €ŀœb€ÂÂ, €ŀœc€ÂÂ, €ŀœd€ and so forth. Different items move at different paces so one entry may move through each category faster than another entry. It is all part of the process. To keep each category together I would sort the spreadsheet on a regular basis. Then I decided it would be better to just create a Macro and click a button to do the sort for me. I then needed to have different categories sorted differently so I added to my sort the columns K and J. What I am now trying to do is enter an additional sort for the items in category €ŀœd€ÂÂ, to be sorted ascending by the value in column I. The number of rows with €ŀœd€ in the cell in column A will vary; sometimes there will be five of them sometimes there may be six or seven. The rows that this applies to will also change; €ŀœd€ may be in column A rows 239, 240, 241 and 242 one day but rows 247, 248 and 249 the next. I want to come up with a way to select all the rows that have the value €ŀœd€ in the cell of column A and sort that selection ascending by the value in column I. I understand the original Range may have been confusing. I think when I originally created this Macro I just inserted a Range larger than I thought I would need. It was fast and dirty but it accomplished what it needed to at the time. I would love to hear any thoughts or suggestions. Thank you. -- Dave Peterson -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
I still don't understand why you can't sort the entire range by column I
first. Then do another sort against the entire range by column A, J, K (or whatever 3 keys are important). I don't know what to tell you. It wasn't working, maybe I was doing something wrong. If I sorted by column A first and then column I everything was out of order and if I sorted by column I first and then by column A everything was out of order. It just wasn't working, again maybe it was just something I was doing wrong. But yes, you can create a range that consists of the first cell in column A that has a D in it and the last cell in column A that has a D in it. This kind of thing would be run after you've sorted by column A. Option Explicit Sub testme() Dim wks As Worksheet Dim RngToSort As Range Dim TopCell As Range Dim BotCell As Range Dim myStr As String Set wks = Worksheets("Sheet1") myStr = "d" With wks With .Range("A:A") Set TopCell = .Cells.Find(what:=myStr, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If TopCell Is Nothing Then MsgBox "No " & myStr & " found in column A" Exit Sub End If Set BotCell = .Cells.Find(what:=myStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False) If BotCell Is Nothing Then 'this can't happen MsgBox "Major error!" Exit Sub End If If TopCell.Address = BotCell.Address Then MsgBox "Only one " & myStr & " found in column A" Exit Sub End If End With 'sort from column A to AC, right? Set RngToSort = .Range(TopCell, BotCell).Resize(, .Range("ac1").Column) With RngToSort .Sort key1:=.Columns(9), Order1:=xlAscending, _ key2:=.Columns(10), order2:=xlAscending, _ header:=xlNo End With End With End Sub THAT WAS BRILLIANT! I has that run right after my prior sort and everything worked perfectly. Thank you. That was exactly what I was looking for. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
Frank
I'm sorry I left you hanging after your initial post. I was out-of-pocket unexpectedly. Send me your file or a sample of your file. Fake the data as you wish. I need only the layout of what you have. Include examples of what you want for each condition. As I understand what you've said so far, you want different parts of your data sorted by different parameters if certain conditions exist. Make sure that what you send me contains all the different conditions you want considered. HTH Otto "FrankM" wrote in message ... I still don't understand why you can't sort the entire range by column I first. Then do another sort against the entire range by column A, J, K (or whatever 3 keys are important). I don't know what to tell you. It wasn't working, maybe I was doing something wrong. If I sorted by column A first and then column I everything was out of order and if I sorted by column I first and then by column A everything was out of order. It just wasn't working, again maybe it was just something I was doing wrong. But yes, you can create a range that consists of the first cell in column A that has a D in it and the last cell in column A that has a D in it. This kind of thing would be run after you've sorted by column A. Option Explicit Sub testme() Dim wks As Worksheet Dim RngToSort As Range Dim TopCell As Range Dim BotCell As Range Dim myStr As String Set wks = Worksheets("Sheet1") myStr = "d" With wks With .Range("A:A") Set TopCell = .Cells.Find(what:=myStr, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If TopCell Is Nothing Then MsgBox "No " & myStr & " found in column A" Exit Sub End If Set BotCell = .Cells.Find(what:=myStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False) If BotCell Is Nothing Then 'this can't happen MsgBox "Major error!" Exit Sub End If If TopCell.Address = BotCell.Address Then MsgBox "Only one " & myStr & " found in column A" Exit Sub End If End With 'sort from column A to AC, right? Set RngToSort = .Range(TopCell, BotCell).Resize(, .Range("ac1").Column) With RngToSort .Sort key1:=.Columns(9), Order1:=xlAscending, _ key2:=.Columns(10), order2:=xlAscending, _ header:=xlNo End With End With End Sub THAT WAS BRILLIANT! I has that run right after my prior sort and everything worked perfectly. Thank you. That was exactly what I was looking for. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort by range
On Apr 2, 2:46*pm, FrankM wrote:
I have a current Macro performing a sort for me ... * * Range("A1:U700").Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range _ * * * * ("J2"), Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, _ * * * * Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ * * * * xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ * * * * DataOption3:=xlSortNormal ... but now I need to insert another sort. Only 3 criteria are allowed. What I want to do is specify after the sort above that if column A is the letter d then the section will be sorted ascending by column I. I hope that makes sense. But Range for this new sort will be constantly changing it could be rows 229 through 234 or it could be rows 230 through 233 or any other possible combination. I don't know if this makes any sense but if anyone has any ideas I would love to hear them. How about instead of sorting column(a), filter it for those rows="d" and copying the visible rows to another sheet then sorting that subset by your three criteria?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Worksheet Range from another worksheet range, Excel 2000 & 2003 | Excel Programming | |||
Search range and sort another range | Excel Programming | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
Sort range changes during sort | Excel Discussion (Misc queries) | |||
sort a range | Excel Programming |