Help - Autofill series based on specified max value

[chong]

Hi all, I am currently running into trouble with one of the tasks I am working on.
I need to autofill column B incrementally (by 1) with reference to the maximum value in the cell in column A.

Col A Col B
5 1
5 2
5 3
5 4
5 5
5 1
5 2
5 3
3 1
3 2
3 3
3 1
3 2
4 1
4 2
4 3
4 4
4 1
4 2
4 3

Is there a macro where I would be able to use? I have thousands of records in my listing.

Regards

 

[p45cal]

In the example which follows, put a 1 in cell B30 then copy the formula in cell B31 down. Later you can copy and Paste-Special|Values to convert the formulae to values:

Excel Workbook
	A	B
30	5	1
31	5	2
32	5	3
33	5	4
34	5	5
35	5	1
36	5	2
37	5	3
38	3	1
39	3	2
40	3	3
41	3	1
42	3	2
43	4	1
44	4	2
45	4	3
46	4	4
47	4	1
48	4	2
49	4	3
May 3

 

[Andrew Poulsom]

If you want a macro:

Sub Test()
    Dim LR As Long
    Dim r As Long
    Dim i As Long
    LR = Range("a" & Rows.Count).End(xlUp).Row
    i = 1
    Cells(1, 2).Value = i
    i = i + 1
    For r = 2 To LR
        If Cells(r, 1).Value = Cells(r - 1, 1).Value Then
            If i <= Cells(r, 1).Value Then
                Cells(r, 2).Value = i
                i = i + 1
            Else
                i = 1
                Cells(r, 2).Value = i
                i = i + 1
            End If
        Else
            i = 1
            Cells(r, 2).Value = i
            i = i + 1
        End If
    Next r
End Sub

 

[chong]

Works like a charm! thanks