Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
I have a button that sorts multiple columns based on one row. in another row
there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Greg,
In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Thank You. i used that on my sample and works perfect! i'm applying this to
my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Greg,
This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Just something to be watchful for...
With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ ..columns(1) would be column B ..columns(2) would be column C .... ..columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Dave,
Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Personally, I like the nested nest.
I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Thanks to both of you!
I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) ..Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
I don't understand.
The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson . -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Maybe i didn't explain myself well enough (i will try again) Column B has the
person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) ..Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson . -- Dave Peterson . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
When you use formulas like this that point at cells (not in the same row) and
sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
You lost me on this reply;
are you saying to add a new column every time i want to sort then delete that column, then reapply the formula to the original column? Sorry i feel i'm running on 3 of eight cylinders today. would it be easier if i sent you the worksheet and you give me pointers of how i should go about doing what i'm doing. i have no problems redesigning and doing this the way you would recommend. Of course me doing all the work, i want to learn as much as i can. i feel as if i may be looking at what i want to do in the wrong way. Thanks again Dave "Dave Peterson" wrote: When you use formulas like this that point at cells (not in the same row) and sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson . -- Dave Peterson . -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Yep. That was what I suggested--as long as your formulas point to cells in
different rows. The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) I'm not sure what's going to happen to those references in that formula after your sort. Greg wrote: You lost me on this reply; are you saying to add a new column every time i want to sort then delete that column, then reapply the formula to the original column? Sorry i feel i'm running on 3 of eight cylinders today. would it be easier if i sent you the worksheet and you give me pointers of how i should go about doing what i'm doing. i have no problems redesigning and doing this the way you would recommend. Of course me doing all the work, i want to learn as much as i can. i feel as if i may be looking at what i want to do in the wrong way. Thanks again Dave "Dave Peterson" wrote: When you use formulas like this that point at cells (not in the same row) and sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson . -- Dave Peterson . -- -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
I reread one of your previous messages and I'm confused about what the problem
is and where these gaps/blanks are coming from. Is your data laid out so that one "record" of information is split over 2 or more rows??? Dave Peterson wrote: Yep. That was what I suggested--as long as your formulas point to cells in different rows. The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) I'm not sure what's going to happen to those references in that formula after your sort. Greg wrote: You lost me on this reply; are you saying to add a new column every time i want to sort then delete that column, then reapply the formula to the original column? Sorry i feel i'm running on 3 of eight cylinders today. would it be easier if i sent you the worksheet and you give me pointers of how i should go about doing what i'm doing. i have no problems redesigning and doing this the way you would recommend. Of course me doing all the work, i want to learn as much as i can. i feel as if i may be looking at what i want to do in the wrong way. Thanks again Dave "Dave Peterson" wrote: When you use formulas like this that point at cells (not in the same row) and sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 John Doe; 678 Jim Smith; 672 Sort the number column largest to smallest; keep any lower number duplicate names sorted on the bottom. Many Thanks in advance Greg . -- Dave Peterson -- Dave Peterson . -- Dave Peterson . -- -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
no only one record per row.
you made a reference about the formula: The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) is there something else i should use. i have no problems starting over on that. Thanks Again, you've been a great help Greg "Dave Peterson" wrote: I reread one of your previous messages and I'm confused about what the problem is and where these gaps/blanks are coming from. Is your data laid out so that one "record" of information is split over 2 or more rows??? Dave Peterson wrote: Yep. That was what I suggested--as long as your formulas point to cells in different rows. The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) I'm not sure what's going to happen to those references in that formula after your sort. Greg wrote: You lost me on this reply; are you saying to add a new column every time i want to sort then delete that column, then reapply the formula to the original column? Sorry i feel i'm running on 3 of eight cylinders today. would it be easier if i sent you the worksheet and you give me pointers of how i should go about doing what i'm doing. i have no problems redesigning and doing this the way you would recommend. Of course me doing all the work, i want to learn as much as i can. i feel as if i may be looking at what i want to do in the wrong way. Thanks again Dave "Dave Peterson" wrote: When you use formulas like this that point at cells (not in the same row) and sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
I'm not sure where your data is and what you're sorting. I was afraid that if
you sorted your data, then that range (B7:B106 or J7:J106) would change to something else--and maybe screw up the results. I'm still confused. If you want to zip a small version of your workbook and email it to me, that's ok. Greg wrote: no only one record per row. you made a reference about the formula: The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) is there something else i should use. i have no problems starting over on that. Thanks Again, you've been a great help Greg "Dave Peterson" wrote: I reread one of your previous messages and I'm confused about what the problem is and where these gaps/blanks are coming from. Is your data laid out so that one "record" of information is split over 2 or more rows??? Dave Peterson wrote: Yep. That was what I suggested--as long as your formulas point to cells in different rows. The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) I'm not sure what's going to happen to those references in that formula after your sort. Greg wrote: You lost me on this reply; are you saying to add a new column every time i want to sort then delete that column, then reapply the formula to the original column? Sorry i feel i'm running on 3 of eight cylinders today. would it be easier if i sent you the worksheet and you give me pointers of how i should go about doing what i'm doing. i have no problems redesigning and doing this the way you would recommend. Of course me doing all the work, i want to learn as much as i can. i feel as if i may be looking at what i want to do in the wrong way. Thanks again Dave "Dave Peterson" wrote: When you use formulas like this that point at cells (not in the same row) and sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. John Doe; 678 Jane Doe; 654 John Doe: 721 Jim Smith; 672 Jim Smith; 673 John Doe; 688 Sam Andrews; 700 I would like the above to sort like this John Doe; 721 Sam Andrew; 700 Jim Smith; 673 Jane Doe; 654 John Doe; 688 -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
Hello Dave,
I read through our post once again. you did say something that rung a bell, maybe a small but important one. "The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first." In column 10 they are grouped together, the problem is a blank row and a record WITHOUT a duplicate both have a value of zero in that column only the first duplicate or duplicates there after get assigned a 1,2,3 etc... and that is why they're being sent to the bottom. .. "I added two more column since the original post so the new sort column is L (12) This is the formula i am using in column L. =SUMPRODUCT(($B$8:$B$107=B8)*($L$8:$L$107L8)) I do have another column that counts the amount of times there is a duplicate in column B. maybe i can add something to that formula to do the same thing. here is that formula. =""& COUNTIF($B$8:B8,B8) Just thinking outside the box a little. I have no problem adding to either formula. Thanks again. i appreciate all your help Greg "Greg" wrote: no only one record per row. you made a reference about the formula: The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) is there something else i should use. i have no problems starting over on that. Thanks Again, you've been a great help Greg "Dave Peterson" wrote: I reread one of your previous messages and I'm confused about what the problem is and where these gaps/blanks are coming from. Is your data laid out so that one "record" of information is split over 2 or more rows??? Dave Peterson wrote: Yep. That was what I suggested--as long as your formulas point to cells in different rows. The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) I'm not sure what's going to happen to those references in that formula after your sort. Greg wrote: You lost me on this reply; are you saying to add a new column every time i want to sort then delete that column, then reapply the formula to the original column? Sorry i feel i'm running on 3 of eight cylinders today. would it be easier if i sent you the worksheet and you give me pointers of how i should go about doing what i'm doing. i have no problems redesigning and doing this the way you would recommend. Of course me doing all the work, i want to learn as much as i can. i feel as if i may be looking at what i want to do in the wrong way. Thanks again Dave "Dave Peterson" wrote: When you use formulas like this that point at cells (not in the same row) and sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting one column, move some duplicates to bottom of sort
I don't have any more suggestions.
Good luck. Greg wrote: Hello Dave, I read through our post once again. you did say something that rung a bell, maybe a small but important one. "The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first." In column 10 they are grouped together, the problem is a blank row and a record WITHOUT a duplicate both have a value of zero in that column only the first duplicate or duplicates there after get assigned a 1,2,3 etc... and that is why they're being sent to the bottom. . "I added two more column since the original post so the new sort column is L (12) This is the formula i am using in column L. =SUMPRODUCT(($B$8:$B$107=B8)*($L$8:$L$107L8)) I do have another column that counts the amount of times there is a duplicate in column B. maybe i can add something to that formula to do the same thing. here is that formula. =""& COUNTIF($B$8:B8,B8) Just thinking outside the box a little. I have no problem adding to either formula. Thanks again. i appreciate all your help Greg "Greg" wrote: no only one record per row. you made a reference about the formula: The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) is there something else i should use. i have no problems starting over on that. Thanks Again, you've been a great help Greg "Dave Peterson" wrote: I reread one of your previous messages and I'm confused about what the problem is and where these gaps/blanks are coming from. Is your data laid out so that one "record" of information is split over 2 or more rows??? Dave Peterson wrote: Yep. That was what I suggested--as long as your formulas point to cells in different rows. The thing that scares me is a formula like: =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) I'm not sure what's going to happen to those references in that formula after your sort. Greg wrote: You lost me on this reply; are you saying to add a new column every time i want to sort then delete that column, then reapply the formula to the original column? Sorry i feel i'm running on 3 of eight cylinders today. would it be easier if i sent you the worksheet and you give me pointers of how i should go about doing what i'm doing. i have no problems redesigning and doing this the way you would recommend. Of course me doing all the work, i want to learn as much as i can. i feel as if i may be looking at what i want to do in the wrong way. Thanks again Dave "Dave Peterson" wrote: When you use formulas like this that point at cells (not in the same row) and sort by this field, you're not (usually) going to get the results you want. I'd suggest that you add a new column Copy|paste values to that new column sort by the new column delete the new column and most likely reapply the formula to the original column. I bet the formula doesn't refer to the cells that you hope it would. Greg wrote: Maybe i didn't explain myself well enough (i will try again) Column B has the person's full name. Column J is a calculate field from some of the previous Columns =SUM(C7,F7:H7) and Column K has this formula =SUMPRODUCT(($B$7:$B$106=B7)*($J$7:$J$106J7)) and my Sort button code is this: Sub SortSaturday() ActiveSheet.Unprotect Password:="password" With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password" End With End With End Sub there are 100 rows to input these records into from Row 7:106. If i put 4 records in (Greg H twice and Brian D twice) with different calculated values ex. Greg H, 707 Greg H, 600 Brian D, 720 Brian D, 653 it will sort this way: row 7 Brian D, 720 row 8 Greg H, 707 row 105 Brian D, 653 row 106 Greg H, 600 rows 9:104 are blank I hope this explains better Thanks Again. i appreciate your time. "Dave Peterson" wrote: I don't understand. The cells that look blank should be grouped together -- at least for column 10. There could be gaps in column 9 since your data is grouped by column 10 first. If you're saying that some blanks in column 10 are at the top and some at the bottom, then some of the cells that look blank are really empty (no formula, no value) and some of your data has formulas that evaluated to "" (or had formulas that evaluated to "" and then converted to values). If this doesn't help (and I'd be surprised if it did!), maybe you could explain what the data looks like after your sort and what you really wanted. Greg wrote: Thanks to both of you! I have one other little tweak question. when this sorts the records, it puts the lower values to the very bottom of the sheet as apposed to right below the other records. so I'm "assuming" that this code is also sorting the blank cells as well. Can i add something else to the code; just so it keeps all the records continuous down the sheet without any empty rows between them? of course keeping the sort order. Below is the code with the changed parameters that seems to be working except for what i described above. Sub SortSaturday() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("K7").End(xlDown)) .Sort Key1:=.Columns(10), Order1:=xlAscending, _ Key2:=.Columns(9), Order2:=xlDescending, _ Header:=xlNo End With End With End Sub I really appreciate all you guys have done. I don't think the MVP's or people that help answer all our questions get the praise they really deserve. THANKS! YOU GUY'S ARE GREAT!!!!!! "Dave Peterson" wrote: Personally, I like the nested nest. I think that .columns() makes it easier to understand, too. Bernie Deitrick wrote: Dave, Thanks for the good point.... that's what I get for writing code without testing. I shouldn't have nested the with structures - using a range object like this should reduce needing to count columns, though. Sub TryNow2() Dim myR As Range With ActiveWorkbook.Worksheets("Saturday") Set myR = .Range(.Range("B7"), .Range("J7").End(xlDown)) myR.Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess myR.AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End Sub Bernie MS Excel MVP "Dave Peterson" wrote in message ... Just something to be watchful for... With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Since you're within the B7:J### "with/end with" structure, the .range("E8") is offset from that range (B7 is what's important). So in your sample code, you're sorting by column F and I (when you look at the column headers). I like this syntax: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub inside this with statement With .Range("B7", .Range("J7").End(xlDown)) .Sort Key1:=.columns(4), Order1:=xlAscending, _ Key2:=.columns(7), Order2:=xlDescending, _ .columns(1) would be column B .columns(2) would be column C ... .columns(7) would be column H (I didn't see what columns the OP mentioned before--so I guessed with my code.) ===== And yes, I changed this: With .Range(.Range("B7"), .Range("J7").End(xlDown)) to With .Range("B7", .Range("J7").End(xlDown)) Just because <vbg. Bernie Deitrick wrote: Greg, This should work, assuming that you put the formulas into column J: Sub TryNow() With ActiveWorkbook.Worksheets("Saturday") With .Range(.Range("B7"), .Range("J7").End(xlDown)) .Sort Key1:=.Range("E8"), Order1:=xlAscending, _ Key2:=.Range("H8"), Order2:=xlDescending, _ Header:=xlGuess .AutoFilter Field:=9, Criteria1:="<6", Operator:=xlAnd End With End With End Sub Change the E of E8 to the column with names, that you want to sort ascending, and the H of H8 to the column with values, where you want to sort descending. Cahnge xlGuess to xlYes if you have headers and xlNo if you don't. HTH, Bernie MS Excel MVP "Greg" wrote in message ... Thank You. i used that on my sample and works perfect! i'm applying this to my actual worksheet but i have some troubles setting this up correctly. 1. The sort area is from B7:J106 (so there is more that just the two Columns to sort but those two Columns i mentioned determine the sort order. i do not want column A sorted. 2. How would i code this to sort and filter? below is what i have so far assigned to a button that sorts the way i want but not sending the lower value duplicates to the bottom of list. Sub SortSaturday() ' ' Sort for Saturday ' ' Range("J7:J106").Select ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Clear ActiveWorkbook.Worksheets("Saturday").sort.SortFie lds.Add Key:=Range("J7"), _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Saturday").sort .SetRange Range("B7:J106") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Once again, MANY THANKS IN ADVANCE! "Bernie Deitrick" wrote: Greg, In the next column - for our example, we will use column C, so use cell C2 - use the formula =SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8B2)) Change the 8 of the $A$8 and $B$8 to be the last row of your data, then copy down. Finally, sort by column C ascending, then column B descending. HTH, Bernie MS Excel MVP "Greg" wrote in message ... I have a button that sorts multiple columns based on one row. in another row there are duplicate values ( Peoples names). i would like to keep all records but would like to send the duplicates to the bottom. sorry I'm not sure of the proper way to ask this. so here is a basic example. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicates in a Column (Without Sorting) | Excel Discussion (Misc queries) | |||
How to Move from bottom of one column to top of next | Excel Discussion (Misc queries) | |||
HOW DO I SORT A COLUMN OF NUMBERS AND HAVE THE DUPLICATES REMOVED | Excel Discussion (Misc queries) | |||
Move to bottom of Column | Excel Programming | |||
Shortcut to move to bottom (last) cell in a column | Excel Discussion (Misc queries) |