Create a subtotal if either of 2 chg
 

Dear Friends,

I have a spreadsheet similar to the following:

Symbol Trans Shares
1 AMD Buy 100
2 AMD Buy 50
3 AMD Buy 200
4 DELL Buy 300
5 DELL Buy 100
6 DELL Sell 500
7 DELL Sell 400
8 DELL Sell 600

I want to write code that will insert two rows and put in a subtotal for the
shares
every time either the value in the Symbol column OR the value in the Trans
column changes.

Thus, I would like to put in a subtotal after row 3, because the Symbol
changes from "AMD" to "DELL".

There should also be a subtotal after row 5 because the Trans changes from
"Buy" to "Sell".

I can write the code that inserts the two lines and creates the subtotal. I
just can't write the code that figures out that this needs to be done after
row 3 and after row 5.

Thanks,
Alan


--
achidsey

Create a subtotal if either of 2 chg
 

Alan,

You can just use the DataSubtotals dialog to add two subtotal rows as you
describe. I find it a little hard to read though with the two subtotals on
different rows. Instead, I'd add a new column to the left of Symbol, named
"Combined," and assuming your Symbol header is now in B1, put this in A2:
=B2 & " " &C2
and fill down. Thus A2 would contain "AMD Buy," A4 would contain "DELL Buy"
etc. I'd then do the DataSubtotal on each change in Combined with a
subtotal for Shares.

hth,

Doug

"achidsey" (notmorespam) wrote in message
...
Dear Friends,

I have a spreadsheet similar to the following:

Symbol Trans Shares
1 AMD Buy 100
2 AMD Buy 50
3 AMD Buy 200
4 DELL Buy 300
5 DELL Buy 100
6 DELL Sell 500
7 DELL Sell 400
8 DELL Sell 600

I want to write code that will insert two rows and put in a subtotal for
the
shares
every time either the value in the Symbol column OR the value in the Trans
column changes.

Thus, I would like to put in a subtotal after row 3, because the Symbol
changes from "AMD" to "DELL".

There should also be a subtotal after row 5 because the Trans changes from
"Buy" to "Sell".

I can write the code that inserts the two lines and creates the subtotal.
I
just can't write the code that figures out that this needs to be done
after
row 3 and after row 5.

Thanks,
Alan


--
achidsey

Create a subtotal if either of 2 chg
 

That works, thanks Doug.

Alan

--
achidsey


"Doug Glancy" wrote:

Alan,

You can just use the DataSubtotals dialog to add two subtotal rows as you
describe. I find it a little hard to read though with the two subtotals on
different rows. Instead, I'd add a new column to the left of Symbol, named
"Combined," and assuming your Symbol header is now in B1, put this in A2:
=B2 & " " &C2
and fill down. Thus A2 would contain "AMD Buy," A4 would contain "DELL Buy"
etc. I'd then do the DataSubtotal on each change in Combined with a
subtotal for Shares.

hth,

Doug

"achidsey" (notmorespam) wrote in message
...
Dear Friends,

I have a spreadsheet similar to the following:

Symbol Trans Shares
1 AMD Buy 100
2 AMD Buy 50
3 AMD Buy 200
4 DELL Buy 300
5 DELL Buy 100
6 DELL Sell 500
7 DELL Sell 400
8 DELL Sell 600

I want to write code that will insert two rows and put in a subtotal for
the
shares
every time either the value in the Symbol column OR the value in the Trans
column changes.

Thus, I would like to put in a subtotal after row 3, because the Symbol
changes from "AMD" to "DELL".

There should also be a subtotal after row 5 because the Trans changes from
"Buy" to "Sell".

I can write the code that inserts the two lines and creates the subtotal.
I
just can't write the code that figures out that this needs to be done
after
row 3 and after row 5.

Thanks,
Alan


--
achidsey