Creating a list

  • Thread starter Thread starter Derrich R.
  • Start date Start date
D

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.
 
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.
 
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
 
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
 
Andy Brown said:
One way (of many, no doubt) -
Click to expand...

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))
Click to expand...

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.
 
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.
 
Back
Top