Find top 10 items in a range
 

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

Find top 10 items in a range
 

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

Regards,
Peter T

"JPS" wrote in message
...
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

Find top 10 items in a range
 

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

Find top 10 items in a range
 

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

"JPS" wrote:

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

Find top 10 items in a range
 

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" wrote:

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

Find top 10 items in a range
 

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" <peter_t@discussions wrote in message
...
Look at the SMALL() and LARGE() worksheet functions.

Regards,
Peter T

"JPS" wrote in message
...
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