Dynamic list?

  • I have a sheet, where I have all my securities and their profit/loss. I would like in another sheet to make a dynamic list of the top 10 Profit/loss positions and buttom 10 Profit/loss positions? I have no clue of how to do so......but are there anyone who could help me.? In the sheet where I have all the positions, i have the name of the securities in one column and the Profit/loss of each security in another......I would like it to appear the same way, but only for the top/buttom 10 positions. For example in two small tables...
    :thanx: in advance

  • I am sure someone can come up with it in a better way, but i have a couple of thoughts on how to do this:


    basic way (won't look very nice, but you could code it to look better), and keeping the data in the same place


    Sort the data into Gain/Loss descending (should give you best gain at the top)
    Use the following line (assuming you have a header row):


    Range("A11", Range("B65536").End(xlUp).Offset(-10, 0)).EntireRow.Hidden = True


    OR


    Use the following:


    Range("A2","B11").copy


    Then paste as values into top ten holdings area


    and Range(Range("A65536").End(xlUP).Offset(-10,0),Range("B65536").End(xlUP)).copy


    Then paste as values into worst ten holdings area

  • I would have thought that this was a job for using ranking, sorting & the advanced filter....


    For those who cannot download, I set up a makeshift list of Securities & P&L in A7:B46


    B7:B46 I named _SecPandL


    In column C I entered =RANK(B7,_SecPandL) and copied down to C46


    I then named the entire table A6:C46 _Table


    The following code then transfers the Top ten to Sheet2 and the Bottom10 to sheet3



    I attach the sample workbook. HTH

  • Thank you for the approaches.........Is it not possible to make an array and save the ten biggeest profits and 10 biggest losses? Then transfer this array to the other sheet......
    I must admit that i do not have any clue of where to start, but i would like to save the securities with P&L in an array......sort the array values and take out the top 10 and buttom 10 securities with the corresponding P&L....

  • but I will have to sort the existing list of securities, right? I do not want to change the order of the securities has been inputed into the system.... As far as I can see......you first add a row, where you rank them and then sort the existing list from this order.....

  • Quote from Turbothorup

    but I will have to sort the existing list of securities, right? I do not want to change the order of the securities has been inputed into the system.... As far as I can see......you first add a row, where you rank them and then sort the existing list from this order.....


    Not necessarily, you could add the RANK column, but do the filter first & then sort the result sets in their new locations, leaving the original table intact (you could even delete the ranking column if you wanted afterwards...)


    Post back if you need help re-arranging the code.

  • Hi,


    excuse me for interrupting.


    easy bit by the formula


    if
    Names in Col.A
    P/L figures in Col.B
    then


    Top 10
    c1:
    =INDEX($A:$B,MATCH(LARGE($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1))
    then fill right and fill down to No.10


    Bottom 10
    =INDEX($A:$B,MATCH(SMALL($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1))
    do the same as above


    note: if there's 2 or more records which have identical P/L amount, only the first record
    will be displayed, therefore you need to modify maybe by cents.


    ???
    jindon

  • Quote from jindon

    Hi,


    note: if there's 2 or more records which have identical P/L amount, only the first record will be displayed...


    Which was why I suggested the RANK function, coupled with the Advanced filter... when dealing with Financial Data, it is more accurate - if you were reporting on your top & bottom 10 securuties, I'm sure you would not want to miss "one or two" out, whatever the reason ;)


    This should cater for duplicate entries & leave your list intact


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!