How to RANK a table based on validations on Multiple Columns???

[Pawiair]

Rank in Column E should be based on the combination of values in Column A, Column B, Columns C and the absolute value in Column D.

E.g. Row 1 to 17, Column-D had a value of 7 (Row 7 - 13) which is the highest for the Project-1 (Column C)

Similarly Row 18 - 32, Column-D had a value of 8 (Row 20 - 27)) which is the highest for the Project-2 (Column C)

The expected outcome is shown in column E (TOP Z).

Any Guidance?

 

[p45cal]

1. What version of Excel are you using?
2. Could you attach a workbook to experiment on rather than a picture of one.?

 

[pecoflyer]

Cross post

Ranking Based on Multiple Criteria

786115 Hi, I am in a strange situation about an EXCEL formula, have spent nearly 10 hours to find something for it, but could not achieve what I wanted to. May be you can help. The below photo will explain you my concern.

www.excelforum.com

 

[Pawiair]

Hi P45cal,

Thanks for your message. I am using Office365.

and the sample file is attached.

Thanks in advance for your time and support.

Thanks,

Pawan Dwiveid

 

[pecoflyer]

Please read https://www.excelguru.ca/content.php?184

 

[Pawiair]

Please read https://www.excelguru.ca/content.php?184

Oh.. Had no clue that these two forums are kind of connected. Surprised & Glad to see that.

 

[vletm]

Pawiair
As You have read Forum Rules before Your the 1st posting, please reread those.
Every Forum has basic same rules, which You have had read too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[p45cal]

In cell E2:
if the column is going to be part of the same table:

=MATCH([@[Count of Tickets]],SORT(UNIQUE(FILTER([Count of Tickets],[Project]=[@Project])),,-1),0)

if the column is not going to be part of the same table:

=MATCH(Table2[@[Count of Tickets]],SORT(UNIQUE(FILTER(Table2[Count of Tickets],Table2[Project]=Table2[@Project])),,-1),0)

and copy down.

The various RANK functions and COUNTIFS were not at all happy to work with dynamic arrays.

ps.

Had no clue that these two forums are kind of connected.

They're not.

 

[pecoflyer]

Oh.. Had no clue that these two forums are kind of connected. Surprised & Glad to see that.

As you have read on XLforum this is NOT the case. All forums have more or less the same rule. Your answer proves you did NOT read what I suggested.

 

[Pawiair]

In cell E2:
if the column is going to be part of the same table:

=MATCH([@[Count of Tickets]],SORT(UNIQUE(FILTER([Count of Tickets],[Project]=[@Project])),,-1),0)

if the column is not going to be part of the same table:

=MATCH(Table2[@[Count of Tickets]],SORT(UNIQUE(FILTER(Table2[Count of Tickets],Table2[Project]=Table2[@Project])),,-1),0)

and copy down.

The various RANK functions and COUNTIFS were not at all happy to work with dynamic arrays.

ps.
They're not.

Hello p45cal,

Big help. This formula works. Thank you very much.

Have a great day.

PD