Ranking & Sorting, but complex

[Edofloat]

Hi All,
Forgive me, this is my first post.
I have read and dug around trying to figure out how to make my sprea
sheet work right and have discovered that I need a little push. Her
is what I am doing...


I have 12 sales people who sell cars and I need to keep track of ho
many new trucks they sell in one column, how many new cars in another
how many used cars, average gross profit, high gross profit, and tota
gross profit. They are ranked from 1 to 12 in each category agains
each other.

The last column keeps track of thier total points (totals of eac
ranking for each sales column) then they are ranked by total points t
determine the overall winner. There are several other columns too bu
for now the solution for this would be enough to get the rest as i
would work the same.

So the sales people are listed in the rows and the cars sold in th
columns. After each category I need to rank each saleperson agains
the others. I used the rank function to rank the sales people from
to 12 (1 is first and 12 is last).

Now for the problem.
The problem comes when two are tied either by selling 0 or selling th
same amount, say 5 units each in a particular column. In order t
break the tie I used the number contained in the total gross column t
determine the sales person who will get ranked higher, in a tie the
the person with more total gross gets ranked higher.

I'm so new to this I'm not sure where to go, I looked over array, an
some of the logical functions and am a little stuck.

I have attached my file so you may look over what I have done thus fa
in case I have explained badly. I am using Win XP Pro and excel 2000.
Thanks for any help

Attachment filename: sales ytd grid test rank.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=43831

 

[Crash]

You may want to use a macro to do this. This will sort on the final
ranking then on the Total Gross.

Sub SortTable()

Range("A3:X14").Select
Selection.Sort Key1:=Range("X3"), Order1:=xlAscending,
Key2:=Range("O3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
End Sub

Jon