Find the minimum value based on another column

willian2142

New Member
Joined
Aug 28, 2017
Messages
7
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).
Sem_t_tulo.png


How can i do this?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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

 
Upvote 0
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.
 
Upvote 0
Another to consider (though, at a glance, it looks like Trebor76's should do the trick):

Enter only:
Code:
=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")))
 
Upvote 0
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) :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top