CODEWITHSUNDEEP
excelvbaexcel-formula
Ghulam
Ghulam

Reputation: 165

Update Cell Content Based on Existing Reference Cells

I have the following situation:

Current Data Structure

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:

Desired 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?

Upvotes: 0

Views: 82

Answers (2)

karma
karma

Reputation: 2009

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

enter image description here

Upvotes: 0

bosco_yip
bosco_yip

Reputation: 3802

In A2, formula copied down :

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

enter image description here

Upvotes: 2

Related Questions