Sort problem for stock trades

voodoo

Board Regular
Joined
Apr 7, 2002
Messages
186
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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?
 
Upvote 0
Your right anno, except that multiples take precedence over singles. So this is not an alpa-numeric sort, but a conditional sort.
Tom
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top