Find top 10 items in a range

  • Thread starter Thread starter JPS
  • Start date Start date
J

JPS

I want to be able to do a report from a single worksheet which will show

The top 10 items
Bottom 10 items

I can use autofilter but autofilter only does one at a time and does it in
the worksheet itself.

I want to be able to show the top and bottom 10 on a seperate list.

Any ideas how this can be done.

Thanks
 
one way

top 10:
=LARGE(yr_data,ROW())

bottom 10:
=SMALL(yr_data,ROW())

put those formulas somewhere in the 1st row and copy down to the 10th
or use relevant numbers instead of ROW()
 
Hi

=LARGE($A$1:$A$100,ROW(A1))
=SMALL($A$1:$A$100,ROW(A1))

Drag down 10 rows for the 10 largest/smallest

Mike
 
I'd do it this way:

1) switch on macro recording
2) generate top 10 items with auto filter
3) copy it to a sheet xx (ctrl-a, ctrl-c, ctrl-v)
4) generate bottom 10 items with auto filter
5) copy it to a sheet xx (ctrl-a, ctrl-c, ctrl-v)
6) stop macro recording
7) adapt the macro according to your needs

Whenever you need to generate this kind of report just run this macro
 
Great thanks.

Now that gives me the value. I need to now find the corresponding Names for
example
Column A Column B
Jas 1500
Peter 1200

For the value I want to be able to get the value in column A.

Now the vlookup is not going to work in this instance as the spreadsheet for
a business reason cannot be sorted on Column B.

Any other ideas?
 
Back
Top