Finding the lowest value from repeated data

[rovaniemi]

Hi everyone,

I am new to this forum and very very bad in excel calculations and formulas. I have hit a road block right now. The issue is I have this excel sheet where I have Column X and Column Y. The data in the column X is Codes which is repeated 4 times over. Column Y consists of Counts.
What I want to find out is for each code what is the corresponding lowest Count value which is greater than zero.

The excel workbook is attached.

[Ace_XL]

Try this array formula

=MIN(IF(($B$2:$B$17=212)*($C$2:$C$17<>0),$C$2:$C$17))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Change the highlighted portion for varying results

[rovaniemi]

Thank you very much for the reply. But the data I have is long and I will have to enter each "highlighted portion" to get the result. If I can get the result for the entire data using just one equation that would be great and that's something I would want. I will be using this formula for another worksheet that I have. Thank you again.

[Ace_XL]

Ok try this instead..this will give MIN values in the Row that the ID appears first and not for other Rows

=IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF(($B$2:$B$17=B2)*($C$2:$C$17<>0),$C$2:$C$17)))

Rmemember to Ctrl+Shift+Enter

[rovaniemi]

Thank you again for the reply. Now, the value associated with Column B is changing as the cell changes, but the value of Column C doesn't change accordingly, when the equation is applied in the Column D. The result remains the same for the entire D column.

For eg: the resultant value of 212 is 3. The values for 223, 230, 242 should be 1, 3, 5 respectively. But their values remains 3

I very much appreciate your patience for replying to my doubts.