Need Help with conditional counting based on cell values

[hyesparky]

I have a worksheet that has 18K rows. Column A is numbered and the values repeat themselves various numbers of times. I want to count the values in the adjacent column and restart the count each time the values change
so:
4 1
4 2
4 3
4 4
4 5
4 6
5 1
5 2
5 3
5 4
6 1
6 2
6
7
7
8
8

I currently only have the values in the LH column. I want a formula that will produce the numbering in the RH column.
Can anyone help me? You guys on here are all so smart and i cant figure out how to do this!

 

[Aladin Akyurek]

Try...

B2, copied down:

=COUNTIF($A$2:A2,A2)

Note that a huge number of CountIf formulas might degrade performance.

 

[hyesparky]

Hi!! Thank you! That ALMOST worked. But the renumbering starts at the last number of the like string instead of the first number of the next value

1	1
1	2
1	3
1	4
1	1
2	2
2	3
2	4
2	5
2	6
2	7
2	8
2	9
2	1
4	2
4	3
4	4
4	5
4	1
5	2
5	3
5	4

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

 

[Aladin Akyurek]

Hi!! Thank you! That ALMOST worked. But the renumbering starts at the last number of the like string instead of the first number of the next value

1	1
1	2
1	3
1	4
1	1
2	2
2	3
2	4
2	5
2	6
2	7
2	8
2	9
2	1
4	2
4	3
4	4
4	5
4	1
5	2
5	3
5	4

<tbody>
</tbody>

The data is asumed to srart at row 2, that is, from A2 on.

 

[circledchicken]

Hi,

As per Aladin's comment on the large number of Countif formulas degrading performance - if this is an issue for you, an alternative might be a VBA procedure as follows (note also assuming data begins in A2):

Sub example()


    Dim vArr        As Variant
    Dim dict        As Object
    Dim lastRow     As Long
    Dim i           As Long
    
    ' assign range to array
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    vArr = Range("A2:B" & lastRow).Value2
    
    ' initialise dictionary
    Set dict = CreateObject("scripting.dictionary")
        
    ' assign count
    With dict
        For i = 1 To UBound(vArr, 1)
            If Not .Exists(vArr(i, 1)) Then
                .Add Key:=vArr(i, 1), Item:=1
            Else
                .Item(vArr(i, 1)) = .Item(vArr(i, 1)) + 1
            End If
            vArr(i, 2) = .Item(vArr(i, 1))
        Next i
    End With
    
    ' print output array
    Range("A2").Resize(UBound(vArr, 1), 2) = vArr
    
End Sub

 

[hyesparky]

Aladin... YOU ARE AWESOME. Thank you so much

Try...

B2, copied down:

=COUNTIF($A$2:A2,A2)

Note that a huge number of CountIf formulas might degrade performance.

 

[Aladin Akyurek]

Aladin... YOU ARE AWESOME. Thank you so much

You are welcome. If CountIf becomes a burden, switch to the routine circled suggests.