VBA Macro to add a relative reference column

Question

I'm trying to make a macro right now that adds a relative reference number based on two values.

Both a client (column B) and batch are present in the dataset (column C). Each client is allowed to have several batches, each of which starts at zero and successively rises the more batches the client has. This indicates that a customer may have batches 0, 1, 2, 3, etc., as well as batches 2, 3, 4, etc.

To generate a relative reference for the client-batches, I'm trying to develop a macro (see column E in the screenshot).

Desired Output

This would have to be done per client.

So far I have only managed to create a macro which selects a specific client ID, I have not figured out how to cycle through them or add the values into column E:

Sub select_relative_column()

Dim ref As Range
Dim ref2 As Range

For i = 1 To 100
        If Cells(i, 2) = 10000201 Then
            Set ref = Range(Cells(i, 1), Cells(i, 5))
            If ref2 Is Nothing Then
                Set ref2 = ref
            Else
                Set ref2 = Union(ref2, ref)
            End If
        End If
    Next i
    ref2.Select
End Sub

Answer 1

You may accomplish this using an Excel formula instead of a macro.

Below is the calculation for row 5 if client is in column C and batch is in column D with data from rows 5 to 24. (can be copied down)

  =$D5-MIN(UNIQUE(FILTER($D$5:$D$24,$C$5:$C$24=$C5,)))+1