Forum Discussion
Selecting the most recent 100 rows according to criteria
I have data with a date and time column. I would like to choose the most recent 100 rows that meet a specific criteria. Have asked an expert and posted in another site, but no answers.
Thanks
Ted
4 Replies
Can this not be done with filters?
It would be useful to see an example of your data and what criteria you are trying to match.
Further, I download these files from my Forex trading software files and do not have much expertise in Excel. Until I hear from you, I will attempt to learn how to use the filters.
Thanks for your reply and sorry I'm late in acknowledging it.
Ted
Ok, the first 100 thing i misread last time, i think the quickest way to do it like this though..
Open you document, Click somewhere on the data (doesn't really matter) press ctrl+t, on the popup click 'my table has headers' and then ok.
Your data should now be in a table and you will have filter options at the top. Now... click the filter on column N and select Newest to oldest. (everything is in date order now). Click the filter on the Profit column and select 'Number filters->greater than. Put in 0 and ok it. Now you have everything that has made a profit in date order.
The last part.. if you want to see only the first 100.. you need something to else to filter. I think the easiest way is to go over to the right hand side, and in cell Y1 Type 'counter' then in Y2 add this formula: =ROW(A1)
It should copy down to the bottom of you table but you might need to confirm you want it to or drag it down. Now filter that column for less than 101.. and thats your last 100 trades.
I've attached the workbook as described.
I appreciate your reply and will attach an xlsx file. I am sorting by the Close Date column (N) and would like to pick the 100 most recent rows that are >0 in the Profit column
