rank desired number of large value without duplicate in other table

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

crossposted: https://www.mrexcel.com/board/thread...table.1210152/

Also cross posted at https://chandoo.org/forum/threads/ra...r-table.48352/

People WILL help if they can - the likelihood is that you have not explained the problem with enough clarity.

Nonetheless, you DO need to abide by our rules. At the moment, you seem unwilling to do so. This cannot be tolerated - sorry.

Try this:

=IFERROR(INDEX(LARGE(IF($B$5:$B$93<>"",$H$5:$H$93),ROW($1:$100)),MATCH(0,INDEX(COUNTIF($T5:$T$5,LARGE(IF($B$5:$B$93<>"",$H$5:$H$93),ROW($1:$100))),0),0)),"")

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

In T5 then copy down

Formula:

=IFERROR(AGGREGATE(14,6,$H$5:$H$93/(ISERROR(MATCH($H$5:$H$93,$M$5:$M5,0))*($D$5:$D$93<>"sum")),1),"")

@Glenn Kennedy
@kvsrinivasamurthy


Hello to two smart and genius men
It was a great pair of solutions
And thank you for solving this problem for me
one thing to ask if possible. is it possible
to do that number of ranks by entering any number in the yellow cell
Number 1 is only the first large number
If the number 10 is entered, ten more will be ranked
Enter the number 40 and the top 40 will be ranked
thank you again

RANGE3.png

In T5 then copy down to T93 (as required)

Formula:

=IF(ROWS($T$5:$T5)>$L$5,"",IFERROR(AGGREGATE(14,6,$H$5:$H$93/(ISERROR(MATCH($H$5:$H$93,$M$5:$M5,0))*($D$5:$D$93<>"sum")),1),""))

Pl see file.
I have removed merged cells by deleting columns. Because it is not possible to use formulas in merged cells.
Avoid using merged cells.
Columns N to Q are formatted for Custom --> # to avoid display of 0.
In N6 and copied to N,O,P and Q columns.

=IF($R6="","",INDEX(C$5:C$93,MATCH($R6,$G$5:$G$93,0)))

@kvsrinivasamurthy

Hello again
And thank you very much for your answer
I added the sum field and the index formula works fine
Now, if I want to rank in such a way that only female gender that have code 1300 is displayed, what should I do?

That is, the rank function ranked by two criteria who have the correct gender of the letter "f" and have code "1300" (column F) and (column B)
Naturally, the index finds the same

It means The result is this
range7.png
not this
raneg6.png

Formulas are changed. Pl see file.
It is always better to give all requirements in the beginning. Every time we have to change the formula. When upload file Pl show some examples also.
Pl come back for any problem.

@kvsrinivasamurthy
I apologize for not asking my question completely

Excellent and good
really thank you

There is only one small problem
If the gender or code cell is empty in the table, it ranks the values that have an empty gender or code

I want to show the result if it is empty according to other conditions

For example, if the code is 1200 and gender is empty, it will rank only the items that have the code 1200
And if the gender option is filled later, the gender will be included among the items with code 1200

In R5

=IF(AND($K$6="",$K$7=""),"",IF(ROWS($R$5:$R5)>$K$5,"",IFERROR(AGGREGATE(14,6,$G$5:$G$93/(ISERROR(MATCH($G$5:$G$93,$R$5:$R5,0))*($D$5:$D$93<>"sum")*(UPPER($F$5:$F$93)=UPPER($K$6))*($B$5:$B$93=$K$7)),1),"")))

@kvsrinivasamurthy

I think I did not convey my meaning correctly
I mean, I explained in the photo
In any case, thank you for your answer
Thank you for your time

if gender and code is empty All data is checked and ranked based on numbers only

range10.png


if remove gender and fill code ranked by code and number
if remove code and fill gender ranked by gender and number
if fill both ranked by code & gender and number
if both is empty ranked by just number Now whatever is gender & code
I want the formula to work either without condition or with one condition or with two conditions
@Glenn Kennedy
hi can you help me?

In R5

=IF(ROWS($R$5:$R6)>$K$5,"",IFERROR(AGGREGATE(14,6,$G$5:$G$93/(ISERROR(MATCH($G$5:$G$93,$R$5:$R6,0))*($D$5:$D$93<>"sum")*((((UPPER($F$5:$F$93)=UPPER($K$6))+(($K$6="")*(ROW($F$5:$F$93)>0)))*(($B$5:$B$93=$K$7)+(($K$7="")*(ROW($F$5:$F$93)>0))))>0)),1),""))

In N5 copied up to Q column.

=IF($R6="","",LOOKUP(1,1/(($G$5:$G$93=$R6)*(((UPPER($F$5:$F$93)=UPPER($K$6))+($K$6="")*(ROW($F$5:$F$93)>0))*(($B$5:$B$93=$K$7)+($K$7="")*(ROW($F$5:$F$93)>0)))),C$5:C$93))

Thank you very much. Have good day.