Extract Top X Maximum Values

How can I extract a list of the top five maximum values from a range of cells. This is similar to using the the "top ten list" in the Auto Filter but I don't want to filter my data in place. Instead I want to extract a list sorted in descending order along with desciption data located in adjacent columns for each maximum value.

In other words take the following list with descriptions in column A and values in column B

Item 1 = 10
Item 2 = 10
Item 3 = 2
Item 4 = 20
Item 5 = 4
Item 6 = 100
Item 7 = 5

and extract the top five values and their desciptions to another location in descending order

Item 6 = 100
Item 4 = 20
Item 1 = 10
Item 2 = 10
Item 7 = 5

I am wondering whether this can be solved with a combination of matching functions and indexing functions or whether I will need VBA code to solve this.

Re: Extract Top Five Maximum Values From A Range

I hate when the solution is so eloquent that I would have had no chance to solve it on my own. Its like getting check mated in the first 10 moves. Thanks VBA Noob.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]VBA Noob,

I found a minor glitch that I can't sort out. If I insert a row above my data range and modify your formula in the following way, it will not show the two largest values;

=INDEX($A$2:$A$8,MATCH(LARGE($B$2:$B$8-ROW($B$2:$B$8)/10^5,ROW(A2)),$B$2:$B$8-ROW($B$2:$B$8)/10^5,0))

I have attached a sample workbook to show how your original formula works and how my modified formula fails.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]OK I found my error. The formula needs to be altered in the following way;

=INDEX($A$2:$A$8,MATCH(LARGE($B$2:$B$8-ROW($B$2:$B$8)/10^5,ROW(A1)),$B$2:$B$8-ROW($B$2:$B$8)/10^5,0))

I have changed the row reference from Row(A2) to Row(A1) in the Match formula. Hope this helps anyone else who looks at this thread.

Re: Extract Top X Maximum Values

Dave,

The other formulas above work fine but I wanted to try your advanced filter approach since I thought that was going to be the best solution when I first tried to solve this problem but I keep getting "reference is not valid" which means I am obviously using this approach wrong.

I am also trying to use this knowlege to extract the 5 smallest non zero values from my data range as well. Any further suggestions would be greatly appreciated. I can attach another sample workbook if that would be easier to show you where I am having problems with your advanced filter solution.

Thanks,
Mark

Re: Extract Top X Maximum Values

Dave,

I've attached a representative workbook sample for the top 5 and bottom five with non zero values.

Thanks,
Mark

Re: Extract Top X Maximum Values

Dave,

This is pretty neat and I am learning more about using the advanced filter but I am still having trouble so I have attached the sample again to show you what my results of the Advanced Filter were. I have included the exact parameters I am using in the Advanced Filter dialog box. For some reason my results for the bottom 5 show the zero value and not the "1" value and they do not appear in ascending order. And the top 5 does not appear in descending order.

Also this approach, although very useful, is not as automated as the array formula approach which has the advantage of updating the results dynamically without the user having to rerun the advanced filter if the the data is modified.

Mark

Re: Extract Top X Maximum Values

Thanks again for your help Dave. Pivot Tables and custom views are all good options but I am trying to stick to the array formulas because of the versatility.

Can anyone help with a formula for extracting the 5 smallest values that are non zero?

=INDEX($D$2:$D$8, MATCH(SMALL($E$2:$E$8 - ROW($E$2:$E$8) / 10^5, ROW() - ROW(D$11) + 1), $E$2:$E$8 - ROW($E$2:$E$8) / 10^5, 0) )

Unfortunately, this one above will extract zero values from a data range as the smallest 5 values.

Re: Extract Top X Maximum Values

This is a great solution for extracting the non zero smallest values but I don't know how to extract the adjacent information to the smallest values. In otherwords, if you have a table of names and new salaries (as I do) and you want to extract the lowest new salaries and their names then you need a vlookup or matching and indexing formula to get the names with the lowest values. I also have zero value salaries within this data from terminated employees whose new salaries are now zero so I need to exclude those names and values when extracting the data.

This array formula works great for extracting the names associated with the five highest values;

=INDEX($A$2:$A$8, MATCH(LARGE($B$2:$B$8 - ROW($B$2:$B$8) / 10^5, ROW() - ROW(A$11) + 1), $B$2:$B$8 - ROW($B$2:$B$8) / 10^5, 0) )

where column A has the names and column B has the salaries and Cell A11 is where you want to start this formula. Now how do I combine your straight forward formula;

=SMALL($B$2:$B$8,COUNTIF($B$2:$B$8,0)+ROW())

with the above formula to find the names associated with the lowest new salaries in column A? A vanilla vlookup formula will have problems indentifying several employees with the same salary (it will likely show the 1st occurance of the name and not subsequent names with the same salaries) but there may be a more crafty way to structure a vlookup with a dynamic range argument to pass by the 1st occurance after it has found it and move on to the next one.

Re: Extract Top X Maximum Values

Thanks again for your recommendations. I like the use of the MIN feature in your pivot table and it makes sense to uncheck the items with zero balances but this would be tedious if you had a few hundred items to scan through and uncheck all the zero balances. I was still hoping someone might have a more automated approach to solving this with formulas. I can always write a macro to do this (I am familar with manipulating and automating pivot table w/ VBA code) but it is nice and usually educational to find alternative solutions that can be gained from just using formulas.