Sort problem for stock trades

[voodoo]

I have to perform a tricky sorting problem in Excel for a stock trading company.

First sort is by Sell / Buy (only two options)so quite easy.

Second sort is by numeric Company code (each listed company has a 4 digit code e.g AOL = 4568, Microsoft = 5685, etc). This is the tricky part. Often these shares a bought in one trade and other times in multiple trades.
I have to sort the multiple trades first follwed by the single trades.

This is an example of how the finished product would look.

Buy/Sell Company Code
S 5684
S 5684
S 5684
S 3256
S 3256
S 3256
S 4582
S 1568
S 5255
B 2260
B 2260
B 2260
B 2260
B 3333
B 3333
B 3333
B 1098
B 9856
B 8506

Anyone with ideas about how to sort in this fashion. I was thinking of first sorting by Buy and Sell and putting each goup in a new worksheet then going through row by row and comparing if the Buy/Sell and code is same as the row below. if it is a single trade cut it and placing at after the final row and working through each worksheet like that. Finally pasting the sorted buys after the sorted sells so it looks like the example above. This seems horribly clumsy but I cant think of any other good ideas.

voodoo

[Tom Schreiner]

Hi
Just wanted to make sure I'm clear on what you are trying to do.
From your example I cannot figure out which order you want the sort performed?
You start with multiple sells descending, then you have a three single sells in no order.
Then multiple buys ascending and end with single buys in no order?
Am I missing your objective here?
Tom

[anno]

i might be missing something, but can't you just use the sort function to do a sort by column a then b in a single operation?

[Tom Schreiner]

Your right anno, except that multiples take precedence over singles. So this is not an alpa-numeric sort, but a conditional sort.
Tom

[anno]

On 2002-04-15 23:17, TsTom wrote:
Your right anno, except that multiples take precedence over singles. So this is not an alpa-numeric sort, but a conditional sort.
Tom

ts tom
i was right too - i was missing something . but if the example voodoo's given is right i think more information is required.

[Aladin Akyurek]

Consider a data set in A1:B19

In C1 enter and copy down:

=COUNTIF($A$1:$B$19,B1)

Select the range A1:C19 then sort:

Column A --> Descending
Column C --> Descending
Column B --> Descending

[voodoo]

My example was a bit sloppy

After Buy/Sell yes the entres should be sorted by ompany code ascending so a better look at the output would be

S 1568
S 1568
S 1568
S 2568
S 2568
S 2568
S 1060
S 2526
S 3565
S 5698
B 1568
B 1568
B 1568
B 2659
B 2659
B 1569
B 2659
B 8856

So it is sort by Sell/Buy,
then within the Sells - multiple trades followed by single trades sorted by company code and then within the Buys - multiple trades followed by single trades sorted by company code.

thanks for the help so far.

[Tom Schreiner]

Voodoo,
I'll mess around with a macro, but hopefully someone will post a better solution for you.
For now, are you able to edit code?
If not, where exactly is your data?
Sheet name containing data...
Column(s)...
Row starting...

Where is your data going?
Repeat same as above.
Thanks,
Tom

Voodoo,
I have to go.
In the unlikely event that someone will be unable to help you, I will write a little macro to help you out tommorrow.
Tom

[ This Message was edited by: TsTom on 2002-04-16 00:54 ]

[Aladin Akyurek]

On 2002-04-16 00:25, voodoo wrote:
My example was a bit sloppy

After Buy/Sell yes the entres should be sorted by ompany code ascending so a better look at the output would be

S 1568
S 1568
S 1568
S 2568
S 2568
S 2568
S 1060
S 2526
S 3565
S 5698
B 1568
B 1568
B 1568
B 2659
B 2659
B 1569
B 2659
B 8856

So it is sort by Sell/Buy,
then within the Sells - multiple trades followed by single trades sorted by company code and then within the Buys - multiple trades followed by single trades sorted by company code.

thanks for the help so far.

See my first reply; do the sorting:

A --> Descending
C --> Descending
B --> Ascending

[voodoo]

Tom

The input data is in an Excel file - actually there about 56 columns of information (Buy/Sell and company code are just two of them). There could be anywhere from 10 to 5000 rows.

Eventually I need to take some of the data from this Excel file an transfer it to another Excel file to become an output file for the customer - but this is not a problem. The biggest problem is sorting the input file properly and without too much trouble.