• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

problems with sorting data.

Im_Offset

Im_Offset

Member
Hello everyone. I found this site yesterday (how did I not know about chandoo earlier ???), and have read many blog and forum entries here, but still cannot figure out how to sort the data I have the way I need to. I have attached a spreadsheet that shows where I am stumbling.

What I'm trying to do is take data about stock market trades, and make some reports. In the spreadsheet, I have input data about 10 trades in cells D4:H13. Ultimately I want to only enter data in columns D through P, and have the spreadsheet do everything else.

So the first sort of the data I want to accomplish is in columns S through W. I want to rank order the open trades (note: all of the trades I have listed are "open" right now) by "strike" (column G). In column V I have already done this by the formula if($V7="","",vlookup($v7,$A$4:$I$18,F$1,False)). Then I want to put the corresponding information (Open Date, Contracts, Exp Date, and Trans Price) in the appropriate columns. I can't use vlookup because it can't find the appropriate row when I have strike prices that are the same. I've read about using an array formula to list multiple instances in a series, but I don't know how to make a formula that will list the data for the next smallest strike price after finding all the similar instances.

Then I want to sort the data a second way, and I have reserved columns AA to AM for this. Here I want to list all of the "closed" trades. (Again, I don't have any closed trades listed yet). Basically, what I have done is used an if statement in each cell that says if the cell in to corresponding row of column P is marked with an "X" to enter the information of that trade in the row. This will give me a table of data of all closed trades, but it will probably have some blank rows in it. I planned to eliminate the blank rows and get that data sorted by "Open Date" by using columns AQ to BC. But I don't know how to do that either.

Can anyone help me with my problem? And by all means, if there is a way to get the data sorted using fewer columns that I am currently using, please feel free to show me how to do that as well.

Thank you in advance. :)

Also, I will take the Excel and VBA class shortly after my upcoming vacation. I'm sure those classes will teach me what I am trying to do, but until then, I am asking for help. :)
 

Attachments

Another option,

1] Rank order number based on Strike, cell R4 formula copy down :

=IF(G4="","",INDEX(C$4:C$18,INDEX(MATCH(LARGE(G$4:G$18+1/ROW($1:$15),ROWS(A$1:A1)),G$4:G$18+1/ROW($1:$15),0),0),0))

2] Open Table, cell S4 formula copy across to X4 and all copydown :

=IF($R4="","",VLOOKUP($R4,$C$4:$I$18,MATCH(S$3,$C$3:$I$3,0),0))

Regards
Bosco
 

Attachments

NARAYANK991 said:
Hi ,

Can you take a look at the uploaded file and confirm whether the results in columns S through W are correct ?

Narayan
Click to expand...
Yes! Those results are correct.Wow! Impressive. However, I don't follow all the way the array formula in column Q. I know this is your helper column for the getting the number of strikes that are the same and finding the next highest strike, but I don't see how it works. Any chance you can help explain it? I'm still digesting the logic in an array formula. Thanks!
 
Hi ,

Your formula in column V is the one which is arranging the strikes in order of their value , inclusive of duplicates ; the LARGE function does this.

The formula in helper column Q is creating an index value using the values in column V ; for each value in column V , the corresponding value in column Q gives the row number where this strike value occurs within your data range.

For example , the strike value of 1350 occurs twice in your data range , once in cell G9 and the second time in cell G13 ; since your data starts in row 4 , G9 is in row 6 , and G13 is in row 10.

These are the two values returned by the formula in column Q.

The reason for creating this helper column is because this same result will be used in deriving the outputs in columns S , T , U and W.

Narayan
 
NARAYANK991 said:
Hi ,

Your formula in column V is the one which is arranging the strikes in order of their value , inclusive of duplicates ; the LARGE function does this.

The formula in helper column Q is creating an index value using the values in column V ; for each value in column V , the corresponding value in column Q gives the row number where this strike value occurs within your data range.

For example , the strike value of 1350 occurs twice in your data range , once in cell G9 and the second time in cell G13 ; since your data starts in row 4 , G9 is in row 6 , and G13 is in row 10.

These are the two values returned by the formula in column Q.

The reason for creating this helper column is because this same result will be used in deriving the outputs in columns S , T , U and W.

Narayan
Click to expand...

Can you do a formula forensics on the array formula in Column Q? I don't understand the how the IF($G$4:$G$18=V4,ROW($G$4:$G$18) - MIN(ROW($G$4:$G$18))+1) works to generate an array of numbers for the SMALL function. Thanks!
 
You must log in or register to reply here.
Back
Top