using excel to pick data from a list, reporting names and integers

[Guest]

Hi,

I'm trying to find out how i can use excel to search through a list of names
and numbers, to find and extract a number that would fit to my criterea.

for example, below is a basic version of my data.

name number

bob 1
mary 2
frank 8
judith 3

now what i would like to do is create some kind of formula that would return
to me, say, the highest number and their name, so it would return:

judith 9

im using it to sort through quite a large list of figures, and i need it to
extract the top 10 figures and names for me.

thanks,

matt

 

[Pete_UK]

You could just sort the data in descending order using number as the
key field.

Or, you could apply Autofilter to the number column and choose Top-10
from the pull-down.

Or, you could use a formula method - look up the LARGE function in
Excel to help you return the top-10 values in another column, and from
these an INDEX & MATCH combination could give you the names
corresponding to those values (it's a bit more difficult if you have
duplicate values in the top-10).

Hope this helps.

Pete

 

[Guest]

well pete, i need to to display the data for me in a separate worksheet, it
cant manipulate or have any effect on the original data.

 

[Pete_UK]

You can CTRL-drag the worksheet tab to create a copy of the sheet or
use File | Save As to give the file a different name. Then you can
manipulate this copy of the data however you wish. When you've
finished, just delete the sheet and the file remains the same (or just
close the file without saving it).

Hope this helps.

Pete

 

[Guest]

Matt: I think I may understand your problem let me re-state it so we are
clear: You want to serach a list on a different worksheet, 'Sheet 1' and
return 10 values that includes the name and the corresponding # from a list
on "Sheet1".

Please send me an e-mail to: Mathew.O'(e-mail address removed) and I'll send you an
excel file with the data and forumlas to accomplish what you want.