Sectional Ranking, is it possible

[Davebro]

Is it possible to copy the ranking function down a column ranking each section separately?

Book1
	A	B
1	18	3
2	17	4
3	26	1
4	2	6
5	21	2
6	16	5
7
8
9
10
11
12	12
13	39
14	34
15	2
16	2
17	23
18
19
20
21
22
23	4
24	34
25	16
26	24
27	12
28	11
29
30
31
32
33
34	17
35	30
36	2
37	20
38	18
39	14
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=RANK(A1,$A$1:$A$6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A33:A42	Expression	=$C35	text	NO
A22:A31	Expression	=$C24	text	NO
A1:A9	Expression	=$C3	text	NO

 

[Joe4]

As long as it follows the same pattern (6 data rows every 11 rows), I would use VBA to set up all those formulas at once like this:

Sub MyRankingMacro()

    Dim r As Long
    Dim rng As String
    
    Application.ScreenUpdating = False
    
'   Set initial row value
    r = 1
    
'   Loop through cells in column A
    Do
'       If column A is blank, exit loo
        If Cells(r, "A") = "" Then Exit Do
'       Build range string
        rng = "$A$" & r & ":$A$" & r + 5
'       Populate formula in column B
        Range("B" & r & ":B" & r + 5).Formula = "=RANK(A" & r & "," & rng & ")"
'       Increment row counter by 11
        r = r + 11
    Loop

    Application.ScreenUpdating = True
    
End Sub

 

[Davebro]

Wow, that's just what I was after, thanks so much for your time and expertise.

 

[Joe4]

You are welcome.
Glad I was able to help!