Assign the Same Sequential Numbers to Duplicates

[paynod]

I'm using Excel 2007. I'm trying to reacreate the pattern shown in the sequence column. Could someone please give me a pointer as to how I could do this?

 

[RoryA]

What version of Excel do you have to work with?

 

[paynod]

Excel version is 2007. I know there are later versions of Excel which harness the UNIQUE Function. I was trying to use something similar with this VBA function in a module. Calling it within the formula but ran into problems and figured there may be a much easier way.

Function UNIQUE(rng As Range) As Variant()
Dim list As New Collection
Dim Ulist() As Variant

'Adding each value of rng to the collection.
On Error Resume Next
For Each Value In rng
'here value and key are the same. The collection does not allow duplicate keys hence only unique values will remain.
list.Add CStr(Value), CStr(Value)
Next
On Error GoTo 0

'Defining the length of the array to the number of unique values. Since the array starts from 0, we subtract 1.
ReDim Ulist(list.Count - 1, 0)

'Adding unique value to the array.
For i = 0 To list.Count - 1
Ulist(i, 0) = list(i + 1)
Next

'Printing the array
UNIQUE = Ulist
End Function

 

[RoryA]

You could use this array entered in C3 then filled down:

=IF(B3=B2,C2,IFERROR(VLOOKUP(B3,B$2:C2,2,0),MAX(IF(A$2:A2=A3,C$2:C2))+1))

 

[paynod]

You could use this array entered in C3 then filled down:

=IF(B3=B2,C2,IFERROR(VLOOKUP(B3,B$2:C2,2,0),MAX(IF(A$2:A2=A3,C$2:C2))+1))

Thanks Rory, that's great. It seems to work as requested.