Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using excel to pick data from a list, reporting names and integers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using excel to pick data from a list, reporting names and integers
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 On Oct 5, 9:45 am, matt wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using excel to pick data from a list, reporting names and inte
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" wrote: 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 On Oct 5, 9:45 am, matt wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using excel to pick data from a list, reporting names and inte
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 On Oct 5, 11:16 am, matt wrote: 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" wrote: 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 On Oct 5, 9:45 am, matt wrote: 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
using excel to pick data from a list, reporting names and integers
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: and I'll send you an excel file with the data and forumlas to accomplish what you want. "matt" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract data value using pick list | Excel Worksheet Functions | |||
create a random list of names from a list of names in EXCEL | Excel Worksheet Functions | |||
random pick names from the list | Excel Discussion (Misc queries) | |||
A list of Consecutive Integers, can I search for missing integers | Excel Worksheet Functions | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) |