Find the minimum value based on another column

[willian2142]

Hi,

I have a table with ID numbers that repeat in some sections, in each of them, it will have a different K value.
There is 3 possibilities, 0 indicates INPUT (IN), the maximum value is LATERAL OUTPUT (OUT L) and the middle one is DIRECT OUTPUT (OUT D).

How can i do this?

 

[Trebor76]

Hi willian2142,

Assuming your data for the result you want is A1:C9 and that the second ID of 2161184 should be OUT D not OUT L as you show it here, put this formula in cell D2...

=IF(COUNTIF($B$2:$B$9,B2)<3,C2,IF(A2=1,"IN",IF(A2=2,"OUT D","OUT L")))

...and copy it down to cell D9.

Regards,

Robert

 

[willian2142]

Hi trebor76,

The IN, OUT D and OUT L is not related to the section number, but to the K.
Assuming the Yellow ones (ID = 2177888) we have:

ID - K
2177888 - 0
2177888 - 0,28
2177888 - 1,78

So, for this case, 0 is IN, 0,28 is OUT D and 1,78 is OUT L

 

[Trebor76]

Ah, my bad. Based on the same range put this array formula* into cell D2...

{=IF(COUNTIF($B$2:$B$9,B2)<3,C2,IF(MIN(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"IN",IF(MAX(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"OUT L","OUT D")))}

...and copy it down to cell D9

HTH

Robert

* Array formulas must be confirmed with CTRL+SHIFT+ENTER after initially typing or subsequently modifying them. When done correctly, Excel will automatically add braces, {}, around the formula. Do not simply type the braces yourself.

 

[inactiveuserps07]

Another to consider (though, at a glance, it looks like Trebor76's should do the trick):

Enter only:

=IF(C2=0,"IN",IF(COUNTIF($B$2:$B$9,B2)=1,C2,IF(C2=SUMPRODUCT(MAX((($B$2:$B$9)=B2)*($C$2:$C$9))),"OUT L","OUT D")))

 

[willian2142]

Thanks man, where do I send the champagne?

 

[Trebor76]

Thanks man, where do I send the champagne?

Ha - As I don't drink and Erik's solution isn't a memory hungry, "fiddly" array formula like mine I say he deserves it (assuming his formula works of course)