Duplicate Entry Summing 1

[mcongdon]

Hey Everyone,
I have a spreadsheet that has been generated by a different program with about 27000 rows. It has 6 columns:
EXDATE,ACCT#,SYMBOL,SHARES,PRICE,and CLIENTCHARGE. I have sorted it by SYMBOL(ascending), EXDATE(ascending), then ACCT#(ascending). What I want to do is look for entries that are kind of duplicates(what we call multiple executions). I am looking for a way to search for entries that have the same EXDATE,ACCT#, and SYMBOL. Then, I would like to take the duplicates and sum the SHARES and the CLIENTCHARGE and create a new row, then delete all the duplicates it was created from. Here is an example:


EXDATE ACCT# SYMBOL SHARES PRICE CLIENT
4/28/08 123456 30274Y473 1963 9.83 2.50
4/28/08 111111 30274Y473 2246 9.86 32.50
4/28/08 111111 30274Y473 2000 9.86 12.41
4/29/08 111111 30274Y473 4000 9.86 26.35

The two trades in the middle are what I am looking for- they have the same EXDATE and ACCT# and SYMBOL. Therefore, what I would like to have as output would be:

EXDATE ACCT# SYMBOL SHARES PRICE CLIENT
4/28/08 123456 30274Y473 1963 9.83 2.50
4/29/08 111111 30274Y473 4000 9.86 26.35
4/28/08 111111 30274Y473 4246 9.86 44.91

I don't know if this is possible, but it would certainly make my life easier. Right now I have to go through all 27000 entries line by line and combine them myself. :-(

Thanks!

 

[SkipVought]

Hi, Check out the PivotTable Wizard, in the Data menu. It can do EXACTLY what you described in a few seconds.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcongdon]

Skip,
This does give me the totals that I want, but not in the form I need it in. I need to keep that worksheet in the same columns and format that it was in. I need it to be in this format to run it through an analysis worksheet I have already designed. Is there any way to keep it in the same format?

 

[SkipVought]

Use Data > Subtotals

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcongdon]

I only know a little bit about pivot tables- I've been messing around with different layouts and options for an hour now and I can't figure out how to make it work. I want it to look like this:

EXDATE ACCT# SYMBOL SHARES PRICE CLIENTCHARGE
4/1/08 CC456 HHH 55 1.73 66.48
4/1/08 CC456 HHH 79 1.73 66.48
4/1/08 CP000 IIK 71 1.86 75.66
4/7/08 XX779 XXX 866 26.30 99.58
4/7/08 XX779 FGH 26 1.99 24.32

I'm getting this instead:

EDATE ACCT# SYMBOL SHARES PRICE CLIENTCHARGE
4/1/08 CC456 HHH 55 1.73 66.48
79 1.73 66.48
CP000 IIK 71 1.86 75.66

4/7/08 XX779 XXX 866 26.30 99.58
FGH 26 1.99 24.32


Any further help would be great. When I try to put client charge and shares as sums in the data area, it tells me there are too many row or column already. No matter what I do, I can't get every field to show in every row like i need it to...

Thanks again

 

[SkipVought]

Then I'd suggest MS Query. faq68-5829

Sum the SHARES and Client.

Generated this in about 30 seconds.
[tt]
EXDATE ACCT# SYMBOL 'Sum of SHARES' PRICE 'Sum of CLIENT'
4/28/2008 111111 30274Y473 4246 9.86 44.91
4/28/2008 123456 30274Y473 1963 9.83 2.5
4/29/2008 111111 30274Y473 4000 9.86 26.35
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcongdon]

Okay, I'm getting excited- This is working, but only for those places where share amounts are greater than zero. How would I set this up to sum the negative share amounts (sells) as well? The client charge is never negative, so thats not an issue..

 

[SkipVought]

"This is working, but only for those places where share amounts are greater than zero."

HUH?

How are your NEGATIVE values stored? Please copy 'n' paste an example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mcongdon]

I figured it out- there were some issues with the other program's output. It had sent some random trades to the worksheet as text. Not too great for a sum. I reformatted everything and it works like a charm. Thanks for all the help Skip!!!