Solved

vba get rows number in each group in excel

Posted on 2013-01-02
5
456 Views
Last Modified: 2013-01-02
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
Comment
Question by:HemlockPrinters
5 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 38738458
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
 

Author Comment

by:HemlockPrinters
ID: 38738673
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
 
LVL 10

Accepted Solution

by:
SANTABABY earned 500 total points
ID: 38738677
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
 
LVL 10

Expert Comment

by:SANTABABY
ID: 38738686
Try this formula for second question:
=IF(MATCH(A2,A:A,0)=ROW(),SUMIF(A:A,A2,B:B),"")
0
 
LVL 16

Expert Comment

by:terencino
ID: 38738725
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now