CODEWITHSUNDEEP
excelexcel-formula
Alien_Explorer
Alien_Explorer

Reputation: 867

Match duplicates with highest numbers [excel formula]?

Can someone help me with the formula here? I have used something like this before ={MAX(IF(...} but it doesn't really fit what I'm doing now.

=MAX(IF(Sheet1!$A$a:$A$1500=Sheet3!D19,Sheet1!$G$1:$G$1500))

I need to identify that I have duplicates in column A, then look at column B and find the highest value for e.g. cat, then match this highest value with the highest in column C (as you can see "cat" has two 140 figures but in C one of them =56, thus I need "OK" next to it). I would be greatly obliged for your help. 

Col A   B   C   Formula?
cat     140 56  OK
cat     140 54  x
cat     87  41  x
cat     91  13  x
cat     100 11  x
dog     230 12  x
dog     230 55  OK
dog     230 45  x
mouse   111 12  x
mouse   123 43  x
mouse   145 55  OK
mouse   103 54  x

enter image description here

Upvotes: 1

Views: 50

Answers (1)

user4039065
user4039065

Reputation:

Try this in C2 and fill down.

=IF(AND(B2=AGGREGATE(14, 7, B:B/(A$1:A$13=A2), 1), C2=AGGREGATE(14, 7, C:C/((A$1:A$13=A2)*(B$1:B$13=AGGREGATE(14, 7, B:B/(A$1:A$13=A2), 1))), 1)), "OK", "x")

enter image description here

Upvotes: 2

Related Questions