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``````
Mar 25, 2023 in Others 234 views

## 1 answer to this question.

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`
• 63,420 points

## VBA Copy/Paste a Range in Next Available Column Then Add a Single Day to One of the Newly Pasted Cells Repeatedly

With a date you can treat it ...READ MORE

## How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

## How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

## VBA Code Help - Need to add a line for each missing date and copy data from cells below

All you have to do is copy ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

## Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

## Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE