Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > sorting one column, move some duplicates to bottom of sort
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 331
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 331
Default 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


.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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


.



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding Duplicates in a Column (Without Sorting) ConfusedNHouston Excel Discussion (Misc queries) 3 February 14th 07 02:24 PM
How to Move from bottom of one column to top of next Bonnie Excel Discussion (Misc queries) 1 December 21st 06 05:18 PM
HOW DO I SORT A COLUMN OF NUMBERS AND HAVE THE DUPLICATES REMOVED Jeff Excel Discussion (Misc queries) 2 August 29th 06 08:38 PM
Move to bottom of Column Nigel Excel Programming 6 December 22nd 05 06:33 PM
Shortcut to move to bottom (last) cell in a column Frustrated Excel Discussion (Misc queries) 1 September 21st 05 03:47 AM


All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement