How to rank filtered cells w/o duplicates w/o breaking sequence?

[Villalobos]

Hi,

My target is to rank numbers (column B) in filtered range without duplicates and without breaking sequence.
The actual formula can handle the visible/hidden rows but cannot avoid the duplicates and unfortunately break the sequence too.

Do you have any idea to solve this issue?

Please, see the details in the attached file.

Thank you in advance any help!

[oeldere]

Please add the given solutions in your earlier threat, since they could be usefull.

Also add manualy the expected result in your excel file.

[Bo_Ry]

Please try at B9

=SUM(N(E9>IF(SUBTOTAL(103,OFFSET($E$8,ROW($E$9:$E$16)-ROW($E$8),)),$E$9:$E$16)))+SUM(--(E9=IF(SUBTOTAL(103,OFFSET($E$8,ROW(E$9:$E9)-ROW($E$8),)),$E$9:$E9)))

Confirm by Ctrl+Shift+Enter

[Villalobos]

Hi oeldere,

Thank you the quick reply.
Please, see again the attached file. I have uploaded the earlier one with expected result (added manually).

The expected result just an example if 2 Material# are not selected.

[oeldere]

E9 =IF(COUNTIF($B$9:$B$16,$B9)=COUNTIF($B$9:$B9,$B9),$D9+ROW()/100000,"") and drag below

F9 =IFERROR(RANK(E9,E9:E16,0),"") and drag below

H9 =IFERROR(INDEX($B$9:$F$16,MATCH(ROW()-8,$F$9:$F$16,0),COLUMN()-7),"") and drag across

See the attached file.

[Villalobos]

@Bo_Ry, @oeldere

Thank you so much for these solutions, I have learnt some new things today.