Numbering fixed amount of Rows

[nymyth]

Hey team,

I have a worksheet that has rows numbered (in col A) from 1 - 77,757. In col B I want to number each group of rows in deciles. So Rows 1 - 7,776 should show me "1" in col B. Rows 7,777-15,52 should show me "2" in column B and so on. Is there a quick formula that can help me achieve this?

Thanks as always.

 

[arthurbr]

Perhaps = CEILING(A1/7776,1) in B1 and pull down ( or auto fill)

 

[Rick Rothstein]

Here is a macro that will fill your range with constants rather than formulas...

Sub Deciles()
  Dim R As Long
  For R = 1 To 69985 Step 7776
    Cells(R, "B").Resize(7776).Value = Int((R + 7776) / 7776)
  Next
  Range("B77758:B77760").Clear
End Sub

 

[nymyth]

Here is a macro that will fill your range with constants rather than formulas...

Sub Deciles()
  Dim R As Long
  For R = 1 To 69985 Step 7776
    Cells(R, "B").Resize(7776).Value = Int((R + 7776) / 7776)
  Next
  Range("B77758:B77760").Clear
End Sub

Both did the trick, thank you so much.

 

[Rick Rothstein]

Both did the trick, thank you so much.

Actually, using Arthur's formula as a base, we can do the macro without using a loop...

Sub Deciles()
  Range("B1:B62208") = [IF({1},CEILING(ROW(A1:A62208)/7776,1))]
  Range("B62209:B77757") = [IF({1},CEILING(ROW(A62209:A77757)/7776,1))]
End Sub