RANK variable blocks of cells between BLANK cells

[BakerBaker]

I would like to RANK individual blocks of cells within a column which will be a variable number between BLANK cells. In the (simple) example I wish to RANK A1 to A4 and then A6 to A11. A formula in B1 would be copied down to the end of the column, which could be several hundred rows.


A1 22 B1 Formula
A2 46
A3 10
A4 8
A5 BLANK
A6 22
A7 12
A8 43
A9 2
A10 19
A11 18
A12 BLANK

etc

 

[Dryver14]

Thanks very much Peter, I really appreciate your time on this, I can only improve because of you and the other wizards on here

 

[KevCarter]

Leave a blank cell above the first value, then use this formula, copied down.

Spreadsheet Formulas

Cell Formula B2 =IF(A2="","",RANK(A2,INDEX(A$1:A2,AGGREGATE(14,6,(ROW(A$1:A1)-ROW(A$1)+1)/(A$1:A1=""),1)+1):INDEX(A2:A$1000,MATCH(TRUE,INDEX(A2:A$1000="",0),0)-1))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Absolutely amazing!!!

 

[BakerBaker]

My initial question received an excellent answer. As an additional question - What needs changing in the formula to sort lowest to highest?

 

[Peter_SSs]

What needs changing in the formula to sort lowest to highest?

Do you mean rank lowest to highest?

If so, add the red part.
=IF(A2="","",RANK(A2,INDEX(A$1:A2,AGGREGATE(14,6,(ROW(A$1:A1)-ROW(A$1)+1)/(A$1:A1=""),1)+1):INDEX(A2:A$1000,MATCH(TRUE,INDEX(A2:A$1000="",0),0)-1),1))

If that is not what you want, please post another small set of sample data and expected results.

 

[BakerBaker]

That's the one. Once again, thank you for the help.

 

[Raida]

Spreadsheet Formulas

Cell Formula B2 =IF(A2="","",RANK(A2,INDEX(A$1:A2,AGGREGATE(14,6,(ROW(A$1:A1)-ROW(A$1)+1)/(A$1:A1=""),1)+1):INDEX(A2:A$1000,MATCH(TRUE,INDEX(A2:A$1000="",0),0)-1))) <tbody> </tbody>

<tbody>
</tbody>

what is the purpose of ROW(A$1)? isn't that just 1 anyway?

 

[Peter_SSs]

what is the purpose of ROW(A$1)? isn't that just 1 anyway?

The purpose is so that if the user later inserts new rows at the top of the worksheet, the formula still works. If we just used 1 instead, the formula would return incorrect results after row insertion.

 

[BakerBaker]

Absolutely amazing!!!

I have used this regularly since 2018. Is there a way to adapt the ranking to give unique rankings - in other words, NOT give equal ranking to duplicate numbers? Thanks

 

[Peter_SSs]

I have used this regularly since 2018.

Which? There have been a couple of different suggestions/discussions in the thread.

Is there a way to adapt the ranking to give unique rankings - in other words, NOT give equal ranking to duplicate numbers?

What a bout some sample data and expected results with XL2BB so we know just what you have and what you are after?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[BakerBaker]

The original post at Mar 22 2018 which begins: I would like to RANK individual blocks of cells within a column etc. This returns equal ranking for duplicate numbers. So, as indicated I would like to adapt the ranking to give unique rankings - in other words, NOT give equal ranking to duplicate numbers? The current formula is:

=IF(D5="","",IF(AB5="","",RANK(AB5,INDEX(AB$4:AB5,AGGREGATE(14,6,(ROW(AB$4:AB4)-ROW(AB$4)+1)/(AB$4:AB4=""),1)+1):INDEX(AB5:AB$993,MATCH(TRUE,INDEX(AB5:AB$993="",0),0)-1))))

84 2 AHLAIN 2 81 3 BETTIES BAY 3 85 1 BOURGEOISIE 1 0 10 DIRE WOLF 10 0 10 DRAMATIC EFFECT 10 76 8 ELEFTHERIA 8 79 7 FLEMISH 7 0 10 GUSHING GOLD 10 80 5 JAKIMA 5 81 3 PANNONICA 3 73 9 RAQIYA 9 80 5 SIOBHANBROGAN 5 69 1 SOUTHWOLD 1 0 4 HES A GENTLEMAN 4 69 1 TELE RED 1 0 4 MOAI 4 64 3 BALQAA 3 0 4 EXTINCTION 4