CODEWITHSUNDEEP
excelparsingfunctionsorting
user1026987
user1026987

Reputation: 73

EXCEL: Keeping only the unique values with the highest value in another column

In my two columns of data I would like to keep only the unique values of ColumnOne that have the highest value in ColumnTwo.

For example 

ColumnOne       ColumnTwo
2                  6
3                  2
7                  8
2                  7
3                  4
7                  3

So in this example my output would be:

      2                  7
      7                  8
      3                  4

because this is the unique values in ColumnOne with the highest values in ColumnTwo.

I tried using the formula 

    "=IF(COUNTIF($A$2:A2,A2)>1,[ IF(B2>____  )1,0 ], 0)"

but I am stuck on what to put in this portion 

   "IF(B2>____)."

This is what I am trying to say in the function: If the value in the first column is repeated more than once

  1. Than if the corresponding B column has a greater value PRINT 1 ELSE PRINT 2

  2. *Else*Print 0

Any help would be really appreciated, Thanks!

Upvotes: 0

Views: 5683

Answers (2)

barry houdini
barry houdini

Reputation: 46341

Based on the original data you could put this formula in C2

=(B2=MAX(IF(A$2:A$7=A2,B$2:B$7)))*(SUM(IF(A$2:A2=A2,IF(B$2:B2=B2,1)))=1)

confirmed with CTRL+SHIFT+ENTER and copied down

That will place a 1 against the first instance of each row containing the MAX B value for each distinct A value, zero otherwise.

You can filter by 1s in column C to see just those rows

Upvotes: 2

JMax
JMax

Reputation: 26591

Let's assume you have already built a first column without duplicates:

Col A   Col B
2        7
7        8
3        4

Here is the formula you can put in col B to get the expected results:

{=MAX((Sheet1!$A$2:$A$7=A2)*Sheet1!$B$2:$B$7)}

This is an array formula you have to validate with Ctrl+Shift+Enter

Upvotes: 0

Related Questions