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

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

Cross post

Hi P45cal,

Thanks for your message. I am using Office365.

and the sample file is attached.

Thanks,

Pawan Dwiveid

#### Attachments

• TEMP111.xlsx
24.5 KB · Views: 6

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

Pawiair
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.
• 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.

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:
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.

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