creating a running tally

K

kevin

hi,

I have a list of names in column B, i want to write something that basically puts a number in column a if there is an entry in B so i have a running count e

1 AB
2 FG
3 XY

any ideas pls.
 
F

Frank Kabel

Hi
the following formulas will put a sequencing number in column A, if
column B is filled:

in A1 enter the following formula
=IF(B1<>"",1,"")

in A2 enter the formula
=IF(B2<>"",MAX($A$1:OFFSET($A2,-1,0))+1,"")
and copy this formula down for as many rows as you like


--
Regards
Frank Kabel
Frankfurt, Germany

kevin said:
hi,

I have a list of names in column B, i want to write something that
Click to expand...
basically puts a number in column a if there is an entry in B so i have
a running count eg
 
F

Frank Kabel

Hi Kevin
one way:

Sub increment_rows()
Dim RowNdx As Long
Dim LastRow As Long
Dim counter As Long
Application.ScreenUpdating = False
counter = 1
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = 1 To LastRow
With Cells(RowNdx, "B")
If .Value <> "" Then
.Offset(0, -1).Value = counter
counter = counter + 1
End If
End With
Next RowNdx
Application.ScreenUpdating = True
End Sub


also deals with blanks rows in between (they are skipped)

--
Regards
Frank Kabel
Frankfurt, Germany

kevin said:
sorry i should have said i am looking for a piece of code that does
Click to expand...
the same thing, like a loop that says if b has a value then a is cell
above + 1 next i type thing. i am a novice but i know the code is
relatively simple just don't know it.
 
Top