# 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...

• Do you mind if the order of the securities is moved around?

• 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

## Files

Kind Regards, Will Riley

• 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....

• What is wrong with the approaches suggested so far ? the use of a filter will beat an array hands down I would have thought..

• 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.....

• As i said before, someone could come up with a much cooler way of doing it.

well done will!

• 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.

• yep.......I follow you.....It actually seems quite straight forward....I will return if I have any problems...thx!

• 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!