[RESOLVED] More Excel Macro Fun!

[DKenny]

OK
Assuming that you have moved the ID and Date columns to a new sheet, the following will index that sheet for you. You will need to change the reference for the rngDataTable variable.

Sub GrudgeIndex()
Dim rngDataTable As Range
Dim rngCell As Range
    
    'This is the 2 column range that contains the ID in
    'the first column and the date in the second column
    Set rngDataTable = ThisWorkbook.Worksheets(1).UsedRange
    
    'Note: I'm assuming that the range has already been sorted
    'by ID then Date - If not you should insert a sort here
    
    'Loop through each PatientID cell
    For Each rngCell In rngDataTable.Columns(1).Cells
        
        'I'm assuming that the 1st row contains headers
        If rngCell.Row <> 1 Then
            
            'If the ID is different that the ID on the
            'previous row, or the date is different from
            'the date on the previous row
            'we restart the index at 1
            If rngCell.Value <> rngCell.Offset(-1, 0) _
            Or rngCell.Offset(0, 1) <> rngCell.Offset(-1, 1) Then
                rngCell.Offset(0, 2).Value = 1
            
            'Otherwise we increment the index by 1
            Else
                rngCell.Offset(0, 2).Value = rngCell.Offset(-1, 2).Value + 1
            End If
        End If
    Next rngCell
    
    
    Set rngDataTable = Nothing
End Sub