Macro to count cells for x number

[moorej]

I'm not for sure if I could use a formula but would rather have a macro. Let's say I have a column with cell values of either an A, B, or C sorted asending. I would like to count the A's for "x" number and repeat until a change in A. Then count B's for "x" number and repeat, then C's. Something like this. The "x" for each will vary form time to time but I can change the "x" value within the macro.

Any input would be very helpful. Thank you.

A's in one column, numbers in another

A:1
A:2
A:3
A:1
A:2
A:3
A:1

Then B's

B:1
B:2
B:1
B:2
B:1
B:2
B:1

Then C's

C:1
C:2
C:3
C:4
C:5
C:1
C:2
C:3
C:4
C:5
C:1
C:2

 

[moorej]

When I paste the formula in column B it kinda works but the cell becomes double spaced and I can't get the other numbers to show up. What am I missing?


A 1
A
A 3
A
A 5
A
A 2
A
A 4
A
A 1
A
A 3
A
A 0
A
A 2
A
A 4
A
[/img]

 

[jindon]

Hi

if yiou tried one of the formula I presented, which one did you try?

There are 4 formula in that message.

rgds,
jindon

 

[moorej]

It was =IF(COUNTIF(A$1:A1,A1)<=5,COUNTIF(A$1:A1,A1),MOD(COUNTIF(A$1:A1,A1),5))

But all gave a double spaced cell with some numbers not visible.

Thanks

 

[jindon]

Hi

I think it is easier with vba

try the code. it will count 1 - 5 repeatedly

Sub count_5()
Dim lastA As Long, i As Long, x As Integer
x = 5     '<----  alter this number to suite
With ActiveSheet
    .Range("b:b").ClearContents
    lastA = .Range("a65536").End(xlUp).Row
    .Range(.Cells(2, 1), .Cells(lastA, 1)).Sort _
        key1:=.Cells(2, 1), order1:=xlAscending
    .Range("b1") = 1
    For i = 2 To lastA
        If .Cells(i - 1, 1) = .Cells(i, 1) Then
            If .Cells(i - 1, 2) + 1 <= x Then
                .Cells(i, 2) = .Cells(i - 1, 2) + 1
            Else
                .Cells(i, 2) = (.Cells(i - 1, 2).Value + 1) Mod x
            End If
        Else
            .Cells(i, 2) = 1
        End If
    Next
End With

hope this helps
jindon

 

[moorej]

Works like a dream. Just what I was looking for. Thanks for everyone's help. I just love this site.