Extract nonzero data

[HMN93]

Hi, I hope you can help me with this.
I have a set of data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Cost
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]13
[/TD]
[/TR]
</tbody>[/TABLE]
How can I extract the nonzero data in the following shape:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]13
[/TD]
[/TR]
</tbody>[/TABLE]
Thank you!

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 109"]
<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>[TR]
[TD="width: 32, bgcolor: transparent"]Code[/TD]
[TD="width: 32, bgcolor: transparent"]Cost[/TD]
[TD="width: 17, bgcolor: transparent"] [/TD]
[TD="width: 32, bgcolor: transparent"]Code[/TD]
[TD="width: 32, bgcolor: transparent"]Cost[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]A[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]D[/TD]
[TD="bgcolor: yellow, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]E[/TD]
[TD="bgcolor: yellow, align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 133"]
<tbody>[TR]
[TD="width: 32, bgcolor: transparent"]Code[/TD]
[TD="width: 32, bgcolor: transparent"]Cost[/TD]
[TD="width: 17, bgcolor: transparent"][/TD]
[TD="width: 32, bgcolor: transparent"]Row #
[/TD]
[TD="width: 32, bgcolor: transparent"]Code[/TD]
[TD="width: 32, bgcolor: transparent"]Cost[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[TD="bgcolor: yellow"]A[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow, align: right"]4[/TD]
[TD="bgcolor: yellow"]D[/TD]
[TD="bgcolor: yellow, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow, align: right"]5[/TD]
[TD="bgcolor: yellow"]E[/TD]
[TD="bgcolor: yellow, align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[TD="bgcolor: transparent"]*****[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]column C[/TD]
[TD]column D[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

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