Excel formula doubt
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.
Attachment | Size |
---|---|
Book1.xlsx | 9.18 KB |
Create pivot table
Create pivot table with X values as rows. Put Y values as values and set it as Min. Set X values as filter and exclude zeros. Let me know your e-mail address if you want completed file to be sent to you.
answer
Thank you Almir for your patience to look in to my issue. I had posted this other forums also as the I wanted and urgent solution. Thank you again.
Answer
Enter unique codes in Col G
Add the following ARRAY formula to H2;
=MIN(IF($B$2:$B$17=G2,1,999)*IF($C$2:$C$17>0,$C$2:$C$17,9999))
Press CTRL-ALT-ENTER instead of just ENTER
Copy the formula down.
Replace 9999 by any large number which will be greater than all your counts in Col C
Thank you very much for the
Thank you very much for the reply. But I am very much confused with "Unique code and your description of H2 and G2. Could you please clarify?