Group values and sum using loop
 

Need some help on this one. I need to do the following using probably
For...Next loop:
Preface: There are two columns (H,I) that has values. Column H ha
dollar amounts and Column I has an identifier number (both formatted a
number).

One identifier number could comprise several dollar amounts. In othe
words, 10 rows could have different dollar amounts in column H, but al
have the same identfier number of "5".

1. Iterate down and capture the first identifier
2. Iterate down and capture the dollar amounts assigned to tha
identifier
3. Insert a row and enter the sum amount in column H.
4. Move to the next row (which should have the next identifier) an
repeat.

Example:

Col H, Col I
H2 = $55.00, I2 = 5
H3 = $25.00, I3 = 5
<Insert Row: H4 = SUM(H2:H3)
H5 = $35.00, I5 = 7
H6 = $15.00, I6 = 7
<Insert Row: H7 = SUM(H5:H6)

Thanks is advance for the expertise!

--
mthoma
-----------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...fo&userid=2564
View this thread: http://www.excelforum.com/showthread.php?threadid=39336

Group values and sum using loop
 

Data=Subtotal

it does it for you.

--
Regards,
Tom Ogilvy


"mthomas" wrote in
message ...

Need some help on this one. I need to do the following using probably a
For...Next loop:
Preface: There are two columns (H,I) that has values. Column H has
dollar amounts and Column I has an identifier number (both formatted as
number).

One identifier number could comprise several dollar amounts. In other
words, 10 rows could have different dollar amounts in column H, but all
have the same identfier number of "5".

1. Iterate down and capture the first identifier
2. Iterate down and capture the dollar amounts assigned to that
identifier
3. Insert a row and enter the sum amount in column H.
4. Move to the next row (which should have the next identifier) and
repeat.

Example:

Col H, Col I
H2 = $55.00, I2 = 5
H3 = $25.00, I3 = 5
<Insert Row: H4 = SUM(H2:H3)
H5 = $35.00, I5 = 7
H6 = $15.00, I6 = 7
<Insert Row: H7 = SUM(H5:H6)

Thanks is advance for the expertise!!


--
mthomas
------------------------------------------------------------------------
mthomas's Profile:
http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=393365

Group values and sum using loop
 

Take a look at Data-Subtotal as a built in Excel feature. It might be
easier than writing code.

--
Cheers
Nigel



"mthomas" wrote in
message ...

Need some help on this one. I need to do the following using probably a
For...Next loop:
Preface: There are two columns (H,I) that has values. Column H has
dollar amounts and Column I has an identifier number (both formatted as
number).

One identifier number could comprise several dollar amounts. In other
words, 10 rows could have different dollar amounts in column H, but all
have the same identfier number of "5".

1. Iterate down and capture the first identifier
2. Iterate down and capture the dollar amounts assigned to that
identifier
3. Insert a row and enter the sum amount in column H.
4. Move to the next row (which should have the next identifier) and
repeat.

Example:

Col H, Col I
H2 = $55.00, I2 = 5
H3 = $25.00, I3 = 5
<Insert Row: H4 = SUM(H2:H3)
H5 = $35.00, I5 = 7
H6 = $15.00, I6 = 7
<Insert Row: H7 = SUM(H5:H6)

Thanks is advance for the expertise!!


--
mthomas
------------------------------------------------------------------------
mthomas's Profile:
http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=393365

Group values and sum using loop
 

Yea, I just decided to record a macro for the Sub Totals feature and
tweak it a bit and it does the job. Because the number of rows vary,
I'm using the lastRow variable to make it dynamic.

Dim lastRow
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("I" & beginRow & "").Select
Selection.Subtotal GroupBy:=9, Function:=xlSum,
TotalList:=Array(8), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Thanks all!


--
mthomas
------------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=393365