CODEWITHSUNDEEP
excelvbaexcel-formula
user13521158
user13521158

Reputation: 3

Generate ID based on another column

I just want to produce a same code for all the blank cells follow the non-blank above cell.

enter image description here

Upvotes: 0

Views: 182

Answers (2)

urdearboy
urdearboy

Reputation: 14580

Assuming the first row of your table is populated with a value you can drop this into the 2nd row (D4 in your photo) and drag down as needed

=IF(B4="",A3,A3+1)

If you did decide to go with VBA you could try this macro. Note this does not need the first row of table to be populated and the numbering will stop at the last value found in the Type column

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim xCell As Range

For Each xCell In ws.Range("A3:A" & ws.Range("B" & ws.Rows.Count).End(xlUp).Row)
    If xCell.Offset(0, 1) <> "" Then
        xCell = Application.WorksheetFunction.Max(ws.Range("A3:A" & xCell.Row)) + 1
    Else
        xCell = xCell.Offset(-1)
    End If
Next xCell

End Sub

enter image description here

Upvotes: 1

Justyna MK
Justyna MK

Reputation: 3563

Welcome to SO,

You can try using a simple COUNTIF approach:

=COUNTIF($B$3:B3,"<>"&"")

enter image description here

This will count how many Solo/Convoys are not blank in a given range. For column E, use =B3&"" to copy the values from column B.

Upvotes: 0

Related Questions