 # MAX values (top10) from 2 columns - Value matches cannot be from the same row.

• Hi All,

It sounds simple and I'm sure it is, but I've gone back and forth last few days but cannot solve it. In a nutshell, I need to find Top 10 values from 2 columns, HOWEVER, I cannot have more than one max value in this list from the same row of the source data, it must be a strict one answer per row from the 10 highest value rows. I hope that makes sense?!

My current Formula below returns values fine but I get a repeat where I don't need it please see the image link below:

Code
``=IF(ROWS(\$J30:J\$30)<=\$H\$28,LARGE(\$K\$3:\$L\$20,ROWS(J30:\$J\$30)),"")``

In this Example Table if the values in the "Count" column (K:K) are greater than a value in the "Max" column (L:L) then it needs use the Values in Count Column (K:K), but do not repeat the value adjacent in Column L:L further down the Top 10 (147 at 20:30:00 is not wanted in this example list but the two highlighted 138's are fine). The cells pictured to the right which are headed Time and Top10 are I29:J39 in the example sheet attached Countsheet.xlsx

Previously I have been running a formula for a while top get top 'x' values from a single column. This formula (in a different file) located in AD6 copied down as far as needed (10rows in this case) uses the number input in AD3 to determine a Top 'x' - The code below worked well for this situation along with a formula in the adjacent cells to extract the Day, Date and Time this max value occurred.

Code
``=IF(ROWS(\$AD\$6:\$AD6)<=\$AD\$3,LARGE(\$W\$2:\$W\$112,ROWS(\$AD\$6:\$AD6)),"")``

Thank you in advance for the help.

• Hi, Sorry Roy.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!