Excel function, incrementing value

[pfsolspangler]

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

 

[Bob Phillips]

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)

 

[pfsolspangler]

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)









- Show quoted text -

Thanks very much. Do I need to name Column A in the excel sheet
TEST_COLUMN. How do I apply the vba code to column 2. Sorry, I'm a
newbie.

-- Dave

 

[pfsolspangler]

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)









- 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

 

[T. Valko]

Here's a formula but this may not be very efficient if you have 1000's of
rows of data:

=COUNTIF(A$1:A1,A1)

Custom format as 0000

 

[Teethless mama]

In B2: =TEXT(COUNTIF(A$2:A2,A2),"0000")

copy down as far as needed

 

[T. Valko]

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)









- 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.

 

[T. Valko]

**********
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.


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.

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.