(Large Data Set) Filtering out Top 10 Highest/Lowest data points by each day?

[Hawk11ns]

- I have historical data for every NASDAQ stock for two years (~50k rows worth).
- I have calculated an 'After-Hours' percentage gain for each of these NASDAQ stocks for the past two years (Column E).
- I need to filter out, by each date, only the top 10 performing After-Hours Gainers.

Data Snippet:

Conversely, in a separate file, I have the same setup above in another file but for losers.
- I have historical data for every NASDAQ stock for two years (~50k rows worth).
- I have calculated an 'After-Hours' percentage loss for each of these NASDAQ stocks for the past two years (Column E).
- I need to filter out, by each date, only the top 10 After-Hours Losers.

Suggestions? Thanks for all your assistance!

 

[pgc01]

Hi

Add a pivot table based on your table.
The pivot table allows you to take the top 10 for each date directly (filter->Value filters->Top 10).

 

[Hawk11ns]

Hi

Add a pivot table based on your table.
The pivot table allows you to take the top 10 for each date directly (filter->Value filters->Top 10).

Thanks - i am not well versed in Pivot Tables though and from working with them in this instance it seems almost impossible to bulk filter the top ten values for each of the days in the data set. Basically, what I need to do is have Excel look at the overall data set and for each date in Column B, return the top 10 highest values in Column E for that date, then move on to the next date and repeat until all dates are cycled through and I have a list of the top 10 values for all dates.

I believe the simplest way to go about this would be to filter the data set by 'Date' and then 'AH Gain (Largest to Smallest),' Copy/paste the dates to a new sheet and remove duplicates, and simply run a macro that will return the top 10 lines of data for each date in the list of indexed dates. I jut don't know where to start.