# Unique Ranking

#### loss1003

##### Board Regular
I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

 Vendor Co Cost Fee Rank Vertox 500 4 BV 1520 3 A&S 3057 2 ISA 5000 1 GCC 250 5 GSI 147 6 Excluded Vendors ISA A&S Vendor Co Cost Fee Rank BV 1520 1 Vertox 500 2 GCC 250 3 GSI 147 4

<colgroup><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
assuming vendor is in A1.

Code:
``=RANK(B2,\$B\$2:\$B\$7)``

assuming vendor is in A1.

Code:
``=RANK(B2,\$B\$2:\$B\$7)``

Thanks, but looking for alittle more. I need to know how to rank by excluding whatever vendors are listed under the excluded vendors.

So if BV was also listed under excluded vendors it would be ommited from the final ranking list
. and the revised list would look like (below)
 Vendor Co Cost Fee Rank Vertox 500 1 GCC 250 2 GSI 147 3

<TBODY>
</TBODY>

maybe something like this assuming your exceptions are in e3:e4

=SUMPRODUCT(--(ISNA(MATCH(\$A\$3:\$A\$8,\$E\$3:\$E\$4,0))),--(B3<\$B\$3:\$B\$8))+1

 Vendor Co Cost Fee Rank Vendor Co Cost Fee Rank Vertox 500 4 Vertox 500 2 BV 1520 3 BV 1520 1 A&S 3057 2 GCC 250 3 ISA 5000 1 GSI 147 4 GCC 250 5 GSI 147 6 Excluded Vendors ISA A&S

<COLGROUP><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 6826" width=192><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1166" span=2 width=33><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3868" width=109><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3612" width=102><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IFERROR(INDEX(\$A\$2:\$A\$7,SMALL(IF(1-ISNUMBER(MATCH(\$A\$2:\$A\$7,\$A\$10:\$A\$11,0)),
ROW(\$A\$2:\$A\$7)-ROW(\$A\$2)+1),ROWS(F\$2:F2))),"")
``````

G2, just enter and copy down:
Rich (BB code):
``````=IF(\$F2="","",VLOOKUP(\$F2,\$A\$2:\$B\$7,2,0))
``````

H2, just enter and copy down:
Rich (BB code):
``````=RANK(\$G2,\$G\$2:\$G\$5)+COUNTIF(\$G\$2:G2,G2)-1
``````

Replies
9
Views
269
Replies
0
Views
590
Replies
3
Views
125
Replies
14
Views
371
Replies
9
Views
417

1,196,206
Messages
6,014,011
Members
441,802
Latest member
Aneurysm

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

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