Update Cell Content Based on Existing Reference Cells

Question

I have the following situation:

I would like have EACH initial Ref. Cell cell and its associated blank cells, for example A2:A7, updated so that its results in the following data structure:

I have tried using formulas containing a combination of COUNT, COUNTBLANK, ROW() but have failed miserably in achieving the desired outcome.

Can you help?

Answer 1

In A2, formula copied down :

="1:"&COUNTA(B$2:B2)&":"&ROW(A1)-MATCH("zz",B$2:B2)+1

Answer 2

If with vba, maybe something like this ?

Sub Macro1()
For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
If cell.End(xlDown).Row = Rows.Count Then Exit Sub
Set oEnd = cell.End(xlDown).Offset(-1, 0)
Set rngNum = cell
num = "'" & cell.Value & ":"
n = Range(cell, oEnd).Rows.Count
For i = 1 To n
rngNum.Value = num & i
Set rngNum = rngNum.Offset(1, 0)
Next i
Next cell
End Sub

But the weakness, the code will stop at the last row value in column A. For example based on your first image, the code will stop at 3:1 while you are expecting this 3:1 row will change to 3:1:1 and the next row value is 3:1:2