Find top 10 items in a range

[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

 

[Peter T]

Look at the SMALL() and LARGE() worksheet functions.

Regards,
Peter T

 

[Jarek Kujawa]

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()

 

[Mike H]

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

 

[Franz Erhart]

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

 

[JPS]

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?

 

[Peter T]

See Chip Pearson's excellent topic "Ranking Data In Lists"

http://www.cpearson.com/excel/rank.aspx

Regards,
Peter T

 

[Jarek Kujawa]

would sth like (in C1):

=OFFSET($A$1,MATCH(B1,$B$1:$B$100,)-1,)

help?