count duplicates in groups of 4

[alexbotea2005]

Hi,
I need some help with vba macro.
I have a table where column B has series of duplicate values. I need to count those duplicates in groups of 4 (column H) and then assign a group index on column I.
The index value will be 1 for most of the groups unless the group is duplicate.
My initial trial was to number the duplicates inside the group, but after this I’m stuck.
Thanks in advance for you help.

[rscsmith]

Hi

Try this:

Sub test2()
Dim lr, i, a As Long, Count As Integer, j, iIndex As Integer
Dim curWks As Worksheet

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Set curWks = ActiveSheet

With curWks
lr = .Range("A50000").End(xlUp).Row

Count = 1
iIndex = 1

For i = 2 To lr
 .Cells(i, 8) = Count
 .Cells(i, 9) = iIndex
 
 
 If .Cells(i, 2) = .Cells(i + 1, 2) Then
 'Increment count if the next row is the same
    Count = Count + 1
    Else
'Otherwise
'Reset iIndex.
iIndex = 1

'Search backwards for the last time
'we saw the value on teh next row. If found, set iIndex
'to this value +1
    For j = i To 2 Step -1
     If .Cells(j, 2) = .Cells(i + 1, 2) Then
      iIndex = .Cells(j, 9) + 1
      Exit For
      End If
      Next j
 
 'Reset Count
    Count = 1
    End If
 
'If we incremented count, and it hits 5, we must be in the
'next group of 4
 If Count = 5 Then
     Count = 1
     iIndex = iIndex + 1
     End If
    
 Next i
 
End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
 End With

End Sub

Hope this helps.

Best regards, Rob.

[alexbotea2005]

Thank you very much Rob,
Everything works fine,
Best Regards
Alex BOtea