Problem removing cells with a value of 0

[kodarr]

In the image I have column B that is filling in 0's on the data I wish to remove and display in column D. So from the example in D I would like to see D2=2, D3=6, & D4=7. The code as I have it works if B has true blank cells in where the 0's are, not "" blanks the functions return.

I tried finding a way to have the function that generates these to make true blanks for this to work but that doesn't seem possible from what I saw on the web. So I need help converting this to remove the 0's instead as I couldn't get Match() to work in place of the isblank().

The data is randomly generated so the values won't always be the same and by the end of the project there will be close to 1,000 cells in the range it will be checking against.

Here is the line of code used
{=IFERROR(INDEX($B$2:$B$17, SMALL(IF(ISBLANK($B$2:$B$17), "", ROW($B$2:$B$17)-MIN(ROW($B$2:$B$17))+1), ROW(A1))), "")}

I am using this as an array with ctrl+shift+enter.

example.jpg

Thanks in advance for help on how to make this happen.

[jason.b75]

Maybe

{=IFERROR(INDEX($B$2:$B$17, SMALL(IF($B$2:$B$17>0,ROW($B$2:$B$17)-MIN(ROW($B$2:$B$17))+1), ROW(A1))), "")}

[JohnTopley]

Try

=IFERROR(INDEX($B$2:$B$17, SMALL(IF($B$2:$B$17>0, ROW($B$2:$B$17)-MIN(ROW($B$2:$B$17))+1), ROW(A2))), "")

Array entered.

[Tony Valko]

Not sure what you're wanting to do. See if this helps.

Array entered**:

=IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(B$2:B$17)*(B$2:B$17>0),ROW(B$2:B$17)),ROWS(A$1:A1))), "")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[kodarr]

Thank you. Using <1 since I wanted to get rid of the 0's worked. >0 just gave me the 0's since it removed the other values.

[JohnTopley]

I don't how a test for >0 can remove values of 2,6 and & 7!

Certainly didn't when I tried it.

[jason.b75]

I don't how a test for >0 can remove values of 2,6 and & 7!

Certainly didn't when I tried it.

I think that might have been aimed at my pre-edit reply, John.

I changed ISBLANK() to >0 but forgot to remove the nullstring true argument, so the formula was effectively doing the opposite of what it should.