Rank by Multi Condition

[Guest]

I am working on reviewing 2000 projects across the globe. I would like rank
the projects based on type & source (Internal and External)

A B C D E
Project Type Source Saving Rank
Pr-1 S I 10 3
Pr-2 S E 11 7
Pr-3 W E 23 11
Pr-4 W E 15 12
Pr-5 S I 16 1
Pr-6 S E 18 4
Pr-7 W I 14 10
Pr-8 S I 15 2
Pr-9 W I 15 9
Pr-10 S E 12 6
Pr-11 S E 13 5
Pr-12 S E 9 8

I want to see the result in colm E by a SINGLE FORMULA.

Thanks

 

[Guest]

You can create a userform that would sort a highlighted range.

There is a useful tutorial on xl-logic.com where I found out how to do it.
#

 

[Chip Pearson]

What is the criteria by which the projects are ranked? Is it a simple
concatenation of type and source? Or is there more to the ranking than that?
You might find the ranking formulas on discussed on my web site useful:
http://www.cpearson.com/excel/rank.htm

I want to see the result in colm E by a SINGLE FORMULA.

Depending on what the ranking rules are, you may need to include a column to
hold intermediate results. This column could,of course, be hidden.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Pete_UK]

Set up a small table somewhere (eg X2:Y5) with these values:

IS 4000
ES 3000
IW 2000
EW 1000

Then in Z2 you can enter the formula:

=VLOOKUP(C2&B2,X$2:Y$5,2,0)+D2

and copy this down for as many projects as you have. You can hide
columns X, Y and Z if you don't want them to be visible. You might
need to increase the values in the table (add zeroes to the end), so
that they are larger than the values you have in your saving column.

Then in E2 enter this formula:

=RANK(Z2,Z$2:Z$13)

with the references changed to suit your data (Z$2:Z$2000 ?), and copy
down column E. Not sure why it had to be a single formula, but this
reproduces the results in your example.

Hope this helps.

Pete

 

[Guest]

Thanks for your response. I did visit the site, prior to this posting.
It is a simple concatenation of type and source. I want to achieve the
result with a single formula.