I need help in VBA code. Here is my excel, the first column is order ID, the second column Item is kind of group on ID column, how can I get number of each group ( same as maximum number in Item column) and write to the calculate column ( only the first row of each group) ? Thanks,

ID Item calculate

1 1 1

2 1 2

2 2

5 1 4

5 2

5 3

5 4

Microsoft Excel

Last Comment

terencino

8/22/2022 - Mon

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

This can be done without VBA, just with a formula. In the Calculate column try

=IF(A2<>A1,COUNTIF(A:A,A2),"")

starting in row 2 and copy down.

This assumes that the data is sorted by the ID column and that there are no blank rows in the data. See attached sample file.

Thanks, this works very well. I have another question:

Here is my excel, the first column is order ID, the second column Qty is kind of group on ID column, how can I get sum of each group and write to the calculate column ( only the first row of each group) ? Thanks,

If you actually did want VBA instead of teylyn's excellent suggestion which I would also recommend, here is an update of the file with sample code

Sub CountID()For Each c In Range("A2:A" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row) i = i + 1 j = j + 1 If c.Value <> c.Offset(1, 0).Value Then Range("A2").Offset(j - i, 2).Value = i i = 0 End IfNext cEnd Sub

This can be done without VBA, just with a formula. In the Calculate column try

=IF(A2<>A1,COUNTIF(A:A,A2)

starting in row 2 and copy down.

This assumes that the data is sorted by the ID column and that there are no blank rows in the data. See attached sample file.

cheers, teylyn