Extract nonzero data

[HMN93]

Hi, I hope you can help me with this.
I have a set of data

Code	Cost
A	10
B	0
C	0
D	15
E	13

<tbody>
</tbody>

How can I extract the nonzero data in the following shape:

A	10
D	15
E	13

<tbody>
</tbody>

Thank you!

 

[markmzz]

Maybe this:

Layout

Code	Cost		Code	Cost
A	10		A	10
B	0		D	15
C	0		E	13
D	15
E	13
*****	*****	**	*****	*****

<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;" span="2"> <tbody> </tbody>

Formula

In D2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX(A$2:A$6,SMALL(IF($B$2:$B$6,ROW($A$2:$A$6)-ROW($A$2)+1),ROWS(D$2:D2))),"")

And copy to the right and down.

Markmzz

 

[markmzz]

Another way:

Layout

Code	Cost		Row #	Code	Cost
A	10		1	A	10
B	0		4	D	15
C	0		5	E	13
D	15
E	13
*****	*****	**	*****	*****	*****

<tbody>
</tbody>

Formulas

In D2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(SMALL(IF($B$2:$B$6,ROW($A$2:$A$6)-ROW($A$2)+1),ROWS(D$2:D2)),"")

And copy down.

In E2 - use Enter to enter the formula

=IF($D2="","",INDEX(A$2:A$6,$D2))

And copy to the right and down.

Markmzz

 

[HMN93]

Thank you It worked!

 

[Momentman]

Advanced Filter should do same, but the values are static unlike the formula approach

 

[ACCERBYSS]

Hey There HMN93

column C

column D

<tbody>
</tbody>

Code	Cost
A	10
B	0
C	0
D	15
E	13

<tbody>
</tbody>

<tbody>
</tbody>

You could try this too

=IF(ROWS(C$4:C4)>COUNTIF($D$4:$D$8,">0"),"",INDEX(C$4:C$8,AGGREGATE(15,6,ROW($D$4:$D$8)-ROW($D$4)+1/($D$4:$D$8>0),ROWS(C$4:C4))))

 

[markmzz]

Thank you It worked!

You are welcome and thanks for the feedback.

Markmzz