Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Sort problem for stock trades

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.




  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •