give unique Values to set of data if rows are continues

Hi
I have some values in Sheet1 Colmn A like this.
Col A
A
A
A
B
B
C
A
B
A
I need to give a unique set of values for the rows which are continues
Col A Result
A 1
A 1
A 1
B 1
B 1
C 1
A 2
B 2
A 3

with the above example, first 3 values are continues so i give 1 for those, the next a appears after some other values so I assign 2 again A appears after some rows of data o I assign 3, similarly the same thing to be done for all the values in Column A
Is this possible in formulas or a macro

I have attached the sample file



Appreciate your help

Hi, Try this Your Data Must start in (Minium of) row 2, Column "A".

Sub MG11Aug22
Dim Rng As Range, Dn As Range, Q
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        .Add Dn.Value, Array(Dn.Value, 1)
        Dn.Next = 1
    Else
        Q = .Item(Dn.Value)
            If Dn.Offset(-1) = Dn.Value Then
                Dn.Next = Q(1)
            Else: Q(1) = Q(1) + 1
                Dn.Next = Q(1)
                .Item(Dn.Value) = Q
            End If
    End If
Next
End With
End Sub

Regards Mick

Hello Mick,

I apologies for my delay in replying.. the code works fine for me.. thanks a lot..

Arvind