create non-zero list

[Kaspars]

Hi there,

Could someone help me with a non-macro solution for this problem?
Please see uploaded file, in range B1:C16 there is data with one column which values are unique and doesn't change and another that is connected to other data and will change and sometimes can be not unique. The problem is to automatically with formulas get a shortened list of all unique values with their corresponding values if the corresponding values are not zero (and updating if C column values are changed). Tried nested IF formulas, but it's way too long and can easily miss some conditions.

Looking forward for some awesome solutions!

 

[shrivallabha]

Here is one solution. In cell E3 CTRL+SHIFT+ENTER i.e. Array Enter following formula:
=IFERROR(INDEX($B$3:$B$16,SMALL(IF($C$3:$C$16<>0,ROW($C$3:$C$16)),ROWS($E$3:E3))-2),"")
If entered correctly Excel will surround with {}. Then copy down.

And then in F3:
=IFERROR(VLOOKUP(E3,$B$3:$C$16,2,0),"")
Then copy down

 

[Kaspars]

This was awesome, thank You very much!

Take care!