Top 5 and bottom 5 filter

**logisticsexcel** · 03-13-2018, 01:37 AM

I have a table and would like to quickly filter the top 5 and bottom 5 variances.

Can a macro help with that?

Attached file shows original data and the result expected

Thanks

**FDibbins** · 03-13-2018, 01:45 AM

while you undoubtedly could do this with code, (not my strong side), I would use a helper column, with this in, say, C2, copied down...
=OR(B2<=SMALL($B$2:$B$15,5),B2>=LARGE($B$2:$B$15,5))
then just filter on TRUE

**kersplash** · 03-13-2018, 01:49 AM

Done with a pivot table.

**logisticsexcel** · 03-13-2018, 01:56 AM

Thanks to both on a quick reply.

Just keen to know how that was done with a pivot?
I am able to understand that 2 pivots are created but then how is the top and bottom 5 filtered quickly?

**kersplash** · 03-13-2018, 02:00 AM

If you click on the filter icon and then go into the value filter you'll see the setting for top or bottom 5.

**jindon** · 03-13-2018, 02:21 AM

Try

Please Login or Register  to view this content.

**logisticsexcel** · 03-14-2018, 01:56 AM

Thanks all

**FDibbins** · 03-14-2018, 02:03 AM

Happy to help and thanks for the feedback

Top 5 and bottom 5 filter

LinkBack

Thread Tools

Rate This Thread

Display

Top 5 and bottom 5 filter

Re: Top 5 and bottom 5 filter

Re: Top 5 and bottom 5 filter

Re: Top 5 and bottom 5 filter

Re: Top 5 and bottom 5 filter

Re: Top 5 and bottom 5 filter

Re: Top 5 and bottom 5 filter

Re: Top 5 and bottom 5 filter

Thread Information

Users Browsing this Thread

Similar Threads

VBA Advanced Filter - Two Filters Without Deleting Bottom Filter Data In Same Column Range

Add Total & Average at Bottom row of ListView And Filter based on criteria

how to filter rows and only have those adding at the bottom

Is it possible to use filter to remove top and bottom 25% of values in pivot table?

Excel 2007 [Auto]Filter ignores bottom row of range

Filter to bottom of data

[SOLVED] No filter results in bottom left hand corner

Bookmarks

Bookmarks

Posting Permissions