Hi all,

I have 2 spreadsheets. 1 of them (called "broker confirm") gives me
information on peoples stocks that have been sold or bought. This
information changes daily. For example, it has a column that tells me
"Shares sold", "Shares Bought" , "Shares Issued" and one field that I
always have to delete called "Journal" that I have to take out before I
can get the sum. The way the broker sends me this file is sorted by
name of the shareholder. So there will be a line of sold, then bought,
then another sold ... I need them sorted by "sold", "bought" and
"issued". The other spreadsheet (called "Proof") needs the sum of just
sold shares, bought shares ... etc.

So, is there a way to make it sort the information by sold, bought ...
etc, get the sum of the individual types and add them to the "proof"
spreadsheet?

Ive added a screencap of the "broker confirm" for better understanding.


Thanks


---
Message posted from http://www.ExcelForum.com/

Hi

I'm not sure I understood the spreadsheet layout of 'broker confirm'.
If it has a column to indicate the type of the transaction ('sold',
'bought', etc.) and another column with the amount you can use the
SUMIF function (regardless of the sorting within this spreadsheet):
=SUMIF($B$1:$B$9999,"sold",$C$1:$C$9999)
if column B stores the transaction type and column C the amount

If your spreadsheet layout differs rom this assumption maybe you can
post an example of your rows/columns. Please in plain text / attach no
files to this NG as most people do not open them

HTH
Frank


Hi all,

I have 2 spreadsheets. 1 of them (called "broker confirm") gives me
information on peoples stocks that have been sold or bought. This
information changes daily. For example, it has a column that tells me
"Shares sold", "Shares Bought" , "Shares Issued" and one field that I
always have to delete called "Journal" that I have to take out before
I can get the sum. The way the broker sends me this file is sorted by
name of the shareholder. So there will be a line of sold, then
bought,
then another sold ... I need them sorted by "sold", "bought" and
"issued". The other spreadsheet (called "Proof") needs the sum of
just
sold shares, bought shares ... etc.

So, is there a way to make it sort the information by sold, bought
....
etc, get the sum of the individual types and add them to the "proof"
spreadsheet?

Ive added a screencap of the "broker confirm" for better
understanding.


Thanks


---
Message posted from http://www.ExcelForum.com/

You could do it without VBA using three tools from the Data menu - Sort,
Subtotals and Filter.

Set up your sheet so the top row has headings, e.g., "transaction type" or
whatever.

1) Sort on the column with "shares bought" "sold" etc
2) Create Subtotals for the same column
3) Turn on Data FilterAutofilter. Then click on the little arrow in the
heading cell at the top of the same column. Choose Custom from the menu
that appears.
In the two empty boxes enter contains and Totals, respectively. Click OK

Your sheet will now be sorted by the transaction type, with nothing showing
but the totals. When you copy it to another sheet, you'll see that it only
copies the cells that were visible after the filter, i.e., only the totals.
This is a (really nice) function of Autofilter.

If i've misunderstood and you wanted the subtotals for type within each
customer's name (it didn't sound like it) then you could do that too. You'd
have to do a two-level sort at the beginning and have subtotals in both
columns.

Let me know how this works for you.

Doug

"rxwillow " wrote in message
...
Hi all,

I have 2 spreadsheets. 1 of them (called "broker confirm") gives me
information on peoples stocks that have been sold or bought. This
information changes daily. For example, it has a column that tells me
"Shares sold", "Shares Bought" , "Shares Issued" and one field that I
always have to delete called "Journal" that I have to take out before I
can get the sum. The way the broker sends me this file is sorted by
name of the shareholder. So there will be a line of sold, then bought,
then another sold ... I need them sorted by "sold", "bought" and
"issued". The other spreadsheet (called "Proof") needs the sum of just
sold shares, bought shares ... etc.

So, is there a way to make it sort the information by sold, bought ...
etc, get the sum of the individual types and add them to the "proof"
spreadsheet?

Ive added a screencap of the "broker confirm" for better understanding.


Thanks


---
Message posted from http://www.ExcelForum.com/

Thanks everyone that responded. I guess im going to give the SUMIF thing
a try. As far as using the auto filter function; I am familiar with it
and use it daily, but im trying to do away with that. Theres a whole
bunch of other things that my office has to do with these and other
spreadsheets and I think I can streamline the whole process with VBA.
Unfortunately, Im the only person that has had some experience with
programming so this whole project is on me. And since I havent been
programming for almost 3 years now, Ill probably be back here with more
syntax questions.

Thanks everyone.


---
Message posted from http://www.ExcelForum.com/

Oh, and an example of the spreadsheet (in text ... sorry about that):

Trade Date Settlement Date Name Trans. Type Amount
01/21/04 01/26/04 <name SELL
$345,678.00
01/21/04 01/26/04 <name BUY
$24,234.98
01/21/04 01/26/04 <name SELL $498.00
01/21/04 01/26/04 <name SELL $222.56
01/21/04 01/26/04 JOURNAL
$789,784.99


Now picture that with about 20-40 more entries all in random order. I
the SUMIF function works the way I think it does ... it think it wil
do fine. Im not too sure though

--
Message posted from http://www.ExcelForum.com

Good luck!

Along the lines of SumIf, you might find the following of interest.
Hopefully not again telling you something you knew, but I found this to be
very helpful.
http://www.microsoft.com/office/prev...s/column10.asp

hth,

Doug

"rxwillow " wrote in message
...
Thanks everyone that responded. I guess im going to give the SUMIF thing
a try. As far as using the auto filter function; I am familiar with it
and use it daily, but im trying to do away with that. Theres a whole
bunch of other things that my office has to do with these and other
spreadsheets and I think I can streamline the whole process with VBA.
Unfortunately, Im the only person that has had some experience with
programming so this whole project is on me. And since I havent been
programming for almost 3 years now, Ill probably be back here with more
syntax questions.

Thanks everyone.


---
Message posted from http://www.ExcelForum.com/