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
.


--