sorting one column, move some duplicates to bottom of sort

[Greg]

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

 

[Bernie Deitrick]

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$8>B2))

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]

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.SortFields.Clear
ActiveWorkbook.Worksheets("Saturday").sort.SortFields.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]

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

 

[Dave Peterson]

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]

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]

Personally, I like the nested nest.

I think that .columns() makes it easier to understand, too.

 

[Greg]

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]

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.

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!!!!!!

 

[Greg]

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$106>J7))
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.

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.

 

[Dave Peterson]

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]

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]

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$106>J7))

I'm not sure what's going to happen to those references in that formula after
your sort.

 

[Dave Peterson]

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???

 

[Greg]

no only one record per row.
you made a reference about the formula: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]

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]

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$107>L8))

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

 

[Dave Peterson]

I don't have any more suggestions.

Good luck.

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$107>L8))

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