"T. Valko" wrote in message
...
wrote in message
...
On Jan 2, 12:27 pm, "Bob Phillips" wrote:
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
Range("B2").Value = 1
For i = 3 To LastRow
If .Cells(i, TEST_COLUMN).Value = _
.Cells(i - 1, TEST_COLUMN).Value Then
.Cells(i, "B").Value = .Cells(i - 1, "B").Value + 1
Else
.Cells(i, "B").Value = 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

...



I have a need to generate an incrementing number in a list. The
number will be 0001, 0002, 0003, etc., until the change occurs on a
value in another column.

In the example below, when WYE465 changes to WYE565, the incrementing
number restarts at 0001. This needs to occur again when the value in
the first column changes to a new value ( e.g., WYE566 ).

Any assistance in creating a formula in excel that will perform this
would be greatly appreciated. Thanks.

Table:
value incrementing count
WYE465 0001
WYE565 0001
WYE565 0002
WYE565 0003
WYE565 0004
WYE565 0005
WYE565 0006
WYE565 0007
WYE565 0008
WYE565 0009
WYE565 0010
WYE565 0011
WYE565 0012
WYE566 0001- Hide quoted text -

- Show quoted text -
**********
OK. Then do I need to name column A TEST_COLUMN? I'm not sure how i
apply this code to column B. I've copied it into VB Editor
successfully.

Thanks,

Dave
**********

The macro assumes your data is in column A.

Const TEST_COLUMN As String = "A" '<=== change to suit

Change the "A" to reflect the actual column your data is in.

The macro also assumes that since the data is in column A, then you want
the count to appear in column B starting in cell B2. Change "B2" to
reflect where you want the count to start.

So, if you want the count in some other column change every instance of
"B" to reflect the column where you want the count to appear.

--
Biff
Microsoft Excel MVP


P.S.

You might also have to change this line:

For i = 3 To LastRow

It would be easier if you just told us where the data is and where you want
the count.

--
Biff
Microsoft Excel MVP