• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Pawiair

New Member
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?


79836
 

p45cal

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

pecoflyer

Active Member
Cross post
 

Pawiair

New Member
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
 

Attachments

vletm

Excel Ninja
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.
  • 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

Well-Known Member
In cell E2:
if the column is going to be part of the same table:
Code:
=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:
Code:
=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.
 
Last edited:

pecoflyer

Active Member
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

New Member
In cell E2:
if the column is going to be part of the same table:
Code:
=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:
Code:
=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
 
Top