CODEWITHSUNDEEP
excelexcel-formulaexcel-2013vba
user3151946
user3151946

Reputation: 99

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

Upvotes: 1

Views: 10429

Answers (2)

barry houdini
barry houdini

Reputation: 46331

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

Upvotes: 3

John Dirk Morrison
John Dirk Morrison

Reputation: 41

The question is a little unclear but if what you mean is to get the 5 highest values of Column A and their corresponding values in Column B then the five highest values in Column B and the corresponding values in Column A then the (non automated) solution is pretty simple.

  1. Click on a cell with a header title in it.
  2. Click on 'Data' in the top menu.
  3. Click on 'Filter' in the 'Sort & Filter' section.
  4. Click on the button on Column A - select 'Sort Largest to Smallest'
  5. Grab the top five values from both columns then click on the button in column B and repeat.

Upvotes: 1

Related Questions