Find and display the top 10 values in a 2 column list.

[Mallycat]

I have done some research on this, and found a solution using Array Formulae
http://www.get-digital-help.com/2009...array-formula/ (section titled Two columns sorting by the second column).

This is what I want, but the formula are very complex. I have tried modifying them for a different input range without much success. What I have is a table with a column of text and a column of numbers. I what to return the text and numbers for the top 10 items in order.

eg

Here are my 2 columns of data
Cat 5
Dog 3
Kitten -2
Snake 9
Cow 12
horse 11
bird 3.2
monkey 3.1
fish -20
whale 0
shark 22.1
parrot 0.51
Coke 111

So I want a formula that will return the following result into a new table 2 x 10 in the sheet while keeping the original table in place.

Coke 111
shark 22.1
Cow 12
horse 11
Snake 9
Cat 5
bird 3.2
monkey 3.1
Dog 3
parrot 0.51

Any help would be appreciated. I am OK with an Array formula as long as I can adjust it to different size source range.

[MarvinP]

Hi Mallycat,

See if a Large function using Row() and an Index Match formula work for you... See the attached.

[benishiryo]

hi MallyCat. since you are unable to modify it, shouldn't you upload an eg of your real scenario so we can help you? i didn't see the link, but see if this attachment helps. i assumed data in A1:B13. and in E1:E10, i did the ranking of the 10 biggest numbers.
=LARGE($B$1:$B$13,ROWS(E$1:E1))
$B$1:$B$13 would be where your numbers are. ROWS(E$1:E1) can be changed to wherever you want to start your formula in. if you want to start in E5, then:
ROWS(E$5:E5)

then in D1 is this array formula:
=INDEX($A$1:$A$13,SMALL(IF($B$1:$B$13=E1,ROW($A$1:$A$13)),COUNTIF(E$1:E1,E1))-ROW($A$1)+1)
$A$1:$A$13 are where the names are
$B$1:$B$13 are where the numbers are
E1 is where my ranked numbers are
COUNTIF(E$1:E1,E1) is wherever you start your ranked numbers
-ROW($A$1) this is whatever row your name starts in

i took into account that your numbers may have duplicates

[Tony Valko]

=INDEX($A$1:$A$13,SMALL(IF($B$1:$B$13=E1,ROW($A$1:$A$13)),COUNTIF(E$1:E1,E1))-ROW($A$1)+1)

Finally!

I see someone else does it the smart efficient way!

Reps headed your way.

[benishiryo]

@Tony:
yep i actually verified with you in 1 thread & went to test it out. and it's faster indeed! thanks. =)

the ranging of whole columns is still a pain though, having to know which formulas are suitable for it. i initially thought all non-array & volatile formulas should be safe, but COUNTIF has its risk too:
http://www.excelforum.com/excel-form...with-date.html

[Mallycat]

Thanks to you all - excellent suggestions. I can work with this.

[Mallycat]

MarvinP, I have never really got the Index/Match thing. Can you please explain how your formula works? I can use it as is, but I would rather learn too.

[Tony Valko]

In Excel 2010 I put random numbers (constants) from 0 to 100 in the range A1:A500.

Then, entered this formula:

=COUNTIF(A1:A500,">=50")

Formula result = 241

Then, using Charles Williams range_timer method, got the calculation times for 5 calculations.

The average of those 5 calculations was: 0.000852 sec

Then, changed the formula to:

=COUNTIF(A:A,">=50")

Got the calculation times for 5 calculations.

The average of those 5 calculations was: 0.000876 sec

As you can see, in this application referring to the entire column had no negative impact on calculation time.

I also tried this in Excel 2013 and discovered that it is "much slower" to calculate.

=COUNTIF(A1:A500,">=50")

Formula result = 232

Average calc time for 5 calculations: 0.002182 sec

=COUNTIF(A:A,">=50")

Average calc time for 5 calculations: 0.002578 sec

Another interesting thing is that in Excel 2013 the individual calc times had a wider range while in Excel 2010 the individual calc times were very consistent.

So, whenever someone makes a claim that this formula is more efficient than that formula you can use the code I linked to and do your own testing to see for yourself.

[MarvinP]

Hi Mallycat,

Match simply looks in a single dimension array (a single row or single column) for something and returns how far from the start it is. The zero as the last argument says to find an "exact" match. So if I wanted to match the Cat in a list of Dog, Cow, Cat, Bear and I started at Dog, the word Cat would be the third word. It's exact match in the list would be 3. Index is the opposite or reciprocal of this. It takes a number instead of an exact word or value and returns that cell in the list.

I'd suggest you read about Index/Match compared to VLookuup at http://www.randomwok.com/excel/how-to-use-index-match/ for a better explanation than I can do here.

[Mallycat]

Great thanks

[benishiryo]

@Tony:
yeah i do tests to ensure, like how i did when you told me about offsetting the rows outside the array. i used the Charles Kyd one to test though. should be roughly the same. haven't got the time to test on this one, but apparently it's got to do with blanks. that's an interesting find on the Excel 2013 though.

@Mallycat:
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.