Ranking

clarkebball22

New Member
Joined
Aug 11, 2014
Messages
16
I have the table below. I would like to automatically rank these in the next columns (F-H). The first column is automatically ranked (already know the formula). Second column is names, 3rd column is companies sold, 4th column is internal information and the 5th column will be total employees at each of the companies. What is the formula to automatically put them in order from most companies sold (tie breaker of total employees)?
RanksNamesCompaniesInternalEmployees
3Name 112120
5Name 2880
7Name 3440
1Name 415150
2Name 512124
4Name 6990
6Name 7660

<tbody>
</tbody>
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Yes, and I don't want to sort anything in the columns that are already there. I would like to have a full separate area where I can pull the number 1, 2, etc.
 
Upvote 0
Assuming your table starts in A1, try this in F2 and copy down:

=VLOOKUP(ROW(A1),$A$2:$B$8,2,0)
 
Upvote 0
Code:
=IF(COUNTIF(C:C,C2)>1,RANK(C2,$C:$C)+0.5,RANK(C2,$C:$C))

As far as inworksheet functions go, that will give a half point to ties. I'm still not quite understanding what you mean by a full separate area. Like a vlookup?
 
Upvote 0
In this case above, I would want in F2 to say "1" and G2 to say "Name 4" and H2 to say "15", etc (as these are the 1st place, 2nd place, etc). Then when it comes to the tie of 12's, I want "Name 5" (and corresponding) listed first and under it "Name 1" (and corresponding)
 
Upvote 0
In this case above, I would want in F2 to say "1" and G2 to say "Name 4" and H2 to say "15", etc (as these are the 1st place, 2nd place, etc). Then when it comes to the tie of 12's, I want "Name 5" (and corresponding) listed first and under it "Name 1" (and corresponding)

In F2, copy over and down.

=VLOOKUP(ROW(A1),$A$2:$C$8,COLUMN(A1),0)
 
Upvote 0
In F2, copy over and down.

=VLOOKUP(ROW(A1),$A$2:$C$8,COLUMN(A1),0)

That gives me the correct order, but doesn't break the ties. It only gives me the first ranking in the row. I also tried the formula for the ties, and that gives all of the ties a 0.5 addition, not just the highest employee count. Any ideas?
 
Upvote 0
That gives me the correct order, but doesn't break the ties. It only gives me the first ranking in the row. I also tried the formula for the ties, and that gives all of the ties a 0.5 addition, not just the highest employee count. Any ideas?

When I paste my formula in F2:H8 I get:

1Name 415
2Name 512
3Name 112
4Name 69
5Name 28
6Name 76
7Name 34

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

What are you expecting to see differently?
 
Upvote 0
When I paste my formula in F2:H8 I get:

1
Name 4
15
2
Name 5
12
3
Name 1
12
4
Name 6
9
5
Name 2
8
6
Name 7
6
7
Name 3
4

<tbody>
</tbody>

What are you expecting to see differently?

The reason I am seeing something different is the ranking, I am seeing a N/A in the 3rd spot because both of the 2nd places are getting a 0.5 added. Thus both becoming 2.5 and screwing up the official rank.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top