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
Dynamic list?
-
-
-
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
Code
Display MoreSub RankFilter() Dim rngTop As Range, rngBot As Range Set rngTop = Sheet2.[A1:C1] Set rngBot = Sheet3.[A1:C1] 'top 10 - sort & filter Range("_Table").Sort Key1:=Range("C7"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Range("_Table").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("D2:D3"), CopyToRange:=rngTop, _ Unique:=False 'bottom 10 - sort & filter Range("_Table").Sort Key1:=Range("C7"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Range("_Table").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("C2:C3"), CopyToRange:=rngBot, _ Unique:=False End Sub
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.... -
-
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
thenTop 10
c1:
=INDEX($A:$B,MATCH(LARGE($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1))
then fill right and fill down to No.10Bottom 10
=INDEX($A:$B,MATCH(SMALL($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1))
do the same as abovenote: 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
Code
Display MoreOption Explicit Sub RankFilter() Dim rngTop As Range, rngBot As Range Set rngTop = Sheet2.[A1:C1] Set rngBot = Sheet3.[A1:C1] 'top 10 - filter Range("_Table").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("D2:D3"), CopyToRange:=rngTop, _ Unique:=False 'bottom 10 filter Range("_Table").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("C2:C3"), CopyToRange:=rngBot, _ Unique:=False 'sort Sheets("Top 10").Select Range("A1:C" & Range("C1").End(xlDown).Row).Select Selection.Sort Key1:=Range("C2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Sheets("Bottom 10").Select Range("A1:C" & Range("C1").End(xlDown).Row).Select Selection.Sort Key1:=Range("C2"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Set rngTop = Nothing Set rngBot = Nothing End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!