Search code examples
excelexcel-formulaexcel-2013vba

How to get the highest values from 2 columns in Excel?

I have a design software which extracts data in to an Excel sheet format The output is divided into 2 columns, each of these columns has more than 1000 rows. To make use of this data I need to summarize it to a maximum of the 5 highest values from both of the 2 columns. Therefore, this doesn't mean that it's the maximum of one column and its corresponding value, but it may mean that the 2nd largest value of column 1 & the 4th largest value of column 2.

For example ( if we quoted some of the output data):

enter image description here

The values i should pick here are:

enter image description here

If there is any possible way to achieve that, it will be great

Thanks ..

example file: http://goo.gl/UIEFEv

example file 2: http://goo.gl/VSvuVf

Solution

  • Here's a formula solution. I used 20 rows and extracted the rows which contain the top 5 for each column - you can extend to as many rows as required.

    With data in A1:B20 use this formula in D1 confirmed with CTRL+SHIFT+ENTER and copied across to E1 and down both columns:

    =IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")

    Note: there are only eight rows extracted because some of the rows contain values in the top 5 for both columns. I added the highlighting in colums A and B to more clearly illustrate

    see screenshot below

    enter image description here

    Edit:

    From the comments below it seems that you want a combination of rows which contain the highest value for that column....and rows which contain the highest total for both columns.

    In the original formula there are two conditions joined with "+", i.e.

    ($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)

    The "+" gives you an "OR" type functionality, e.g. in this case rows are included if individual values are in the top 5 in that particular column. You can add other conditions, so if you want to also add any rows which are in the top 5 considering the total of both columns then you can add another "clause", i.e.

    ($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)+($A$1:$A$20+$B$1:$B$20>=LARGE($A$1:$A$20+$B$1:$B$20,5))

    ....and including that in the complete formula you get this version:

    =IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5))+($A$1:$A$20+$B$1:$B$20>=LARGE($A$1:$A$20+$B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")

    You could refine that further by using combinations of + and * (for AND), e.g. for the new condition you might only want to include rows with a total in the top 5 if one of the single values is in the top 10 for that column...

    Explanation:

    The above part shows how you can use + for the OR conditions. In the formula if those conditions are TRUE then the IF function returns the "relative row number" of the range (using ROW(A$1:A$20)-ROW(A$1)+1).

    SMALL function then extracts the kth smallest value, k being defined by ROWS(D$1:D1) which starts at 1 in D1 (or E1) and increments by 1 each row.

    INDEX function then takes the actual value from that row.

    When you run out of qualifying rows SMALL function will return a #NUM! error which IFERROR here converts to a blank