Sort By Highest With Formula

**Kumara_faith** · 04-07-2008, 04:16 PM

Hi,

I have in table 1 the data for error points by staff. In table 2, I would like to use a formula to populate the data from table 1 by the highest error point.

I tried using a IF and LARGE formula but I cant get it right.

Table 1 is populated automatically at my work place and the report is refreshed everyday. I have to use the sort function manually everyday and therefore I was wondering if I can formula this data in table 2 by the highest error points so that everytime the report in table 1 is refreshed, it automatically sorts by highest error points in table 2.

Appreciate help and thanks in advance.

**NBVC** · 04-07-2008, 04:23 PM

See attached.

Formula in F5:

Please Login or Register  to view this content.

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

Formula in G5:

Please Login or Register  to view this content.

confirmed with just ENTER.

Both formulas copied down as far as necessary.

**Kumara_faith** · 04-07-2008, 05:01 PM

NBVC,

This is amazing. I was'nt sure if this was possible. Thank you for your time and patience.

I would like to learn more of how the formula works. Would you be able to explain to me how the formula works ?

**NBVC** · 04-07-2008, 05:04 PM

I'm about to head off... but I can reply tomorrow....

**NBVC** · 04-08-2008, 08:12 AM

As promised, here's an explanation of the formulas...

first the easy one...

Please Login or Register  to view this content.

This as you probably know, when copied down just lists the largest to the smallest values in the specified range. ROWS($A$1:$A1) acts as the k value, in the first row it evaluates to 1 and so gives largest value, in the 2nd row it evaluates to 2 and so give second largest value and so on....

the second formula:

Please Login or Register  to view this content.

is an array formula... and is used instead of a much simpler Index/Match formula because there is potential for repeated values from column C.
The regular Index/Match formula (e.g. =Index($B$5:$B$17,Match(G5,$C$5:$C$17,0))) would work if there was a guarantee that no numbers would repeat in the list... this formula only finds the first match and so will repeat the same text string if repeated numbers occur.

So the array formula works almost the same way, except it employees the Small function to get the consecutive matches instead of only the first.

This part of the formula:

Please Login or Register  to view this content.

gets the position within the range, much like the Match() function does. So it says. If the values in C5:C17 equal what's in G5, record the position number with ROW($C$5:$C$17)-ROW($C$5)+1. The COUNTIF($G$5:G5,G5) part is the k value for the Small() function. It determines k based on how many repeats of the value in G5 exist in the list. The first time it comes to a repeated value, k evaluates to 1, the second time, countif() counts 2 and so k is 2, and so on.

I'm not the best at explaining, but I hope this does help you understand it better. To see the formula in action go to Tools|Formula Auditing|Evaluate Formula... and keep clicking Evaluate... you will see how the formula comes to the final result.