Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

vba get rows number in each group in excel

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
0
HemlockPrinters
Asked:
HemlockPrinters
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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.

cheers, teylyn
BlockRows.xlsx
0
 
HemlockPrintersAuthor Commented:
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,

ID     Qty    calculate

1       1          1

2       3         5

2       2      

5      1          6

5      3      

5      1

5      1
0
 
SANTABABYCommented:
Just in case your IDs are not sorted, the following can help:
Formula for row2, copy & paste for other rows.
=IF(MATCH(A2,A:A,0)=ROW(),COUNTIF(A:A,A2),"")
0
 
SANTABABYCommented:
Try this formula for second question:
=IF(MATCH(A2,A:A,0)=ROW(),SUMIF(A:A,A2,B:B),"")
0
 
terencinoCommented:
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 If
Next c
End Sub

Open in new window

BlockRows.xlsm
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now