Creating a list

[Derrich R.]

I want to create a list of, say, the Top 10 items in my
spreadsheet.

First of all, I want the list to show names in Column A
based on their rank in Column Z.

Let's say Column A has 140 entries. And let's use Top
10%...so, 14 items (or names). Can I enter a function
into 20 cells that says something like "find the #1 item
in Column A according to the data in Column Z and place it
in this cell". The next cell (underneath that) would say
find the #2 item...", etc.

Column Y on my spreadsheet contains a RANK function right
now, so the number returned is a "number". Column Z
contains a PERCENTILE function, and the number returned is
a %. Which would be easier to base the decision upon?
Does it matter?

If you need more info, let me know. And thanks for your
help.

 

[Richard O. Neville]

Maybe I'm missing something, but this sounds like a simple sort. Your sort
area must include columns A through Z, and down to the last row. Once you
have selected the cells to be sorted, select Data-Sort and make the
appropriate choices--obviously, you must sort by Column Z. This operation
will sort all the data, and if you don't need anything but the first 20, you
can delete or hide the rest of the rows.

 

[Andy Brown]

One way (of many, no doubt) -

=INDIRECT("A"&MATCH(LARGE($Z$1:$Z$140,*1*),$Z$1:$Z$140,0))

to return #1. *DON'T* include the *s, that's the rank bit. So #2 would be

=INDIRECT("A"&MATCH(LARGE($Z$1:$Z$140,2),$Z$1:$Z$140,0))

, etc.

If your "top list" started in row 1 you could use
=INDIRECT("A"&MATCH(LARGE($Z$1:$Z$140,ROW()),$Z$1:$Z$140,0))

and copy it down.

Rgds,
Andy

 

[Andy Brown]

Sorting is fine - *if* you don't need to keep the original data order. If
you do, add an index column *before* the sort and make sure you include it
in the sort.

Rgds,
Andy

 

[Harlan Grove]

One way (of many, no doubt) -

Rather a restrictive way since it requires that the column A and Z data both
begin in row 1.

=INDIRECT("A"&MATCH(LARGE($Z$1:$Z$140,*1*),$Z$1:$Z$140,0))

to return #1. *DON'T* include the *s, that's the rank bit. So #2 would be

=INDIRECT("A"&MATCH(LARGE($Z$1:$Z$140,2),$Z$1:$Z$140,0)) ....
If your "top list" started in row 1 you could use
=INDIRECT("A"&MATCH(LARGE($Z$1:$Z$140,ROW()),$Z$1:$Z$140,0))

The main alternative would be

=INDEX($A$1:$A$140,MATCH(LARGE($Z$1:$Z$140,N),$Z$1:$Z$140,0))

which requires only changing the range references to use it on ranges
anywhere in the worksheet.

 

[Derrich R]

I'll try this. A sort just isn't what I'm looking for. I
can easily do that. I want a sheet that will
automatically do this for me once the sheet is populated
with the data.

Thanks, everyone.