Remove the value if condition meet

[Vishaal]

Hi,

Thanks in advance,

Need help to remove the value in coloumn in following sheet if only "0" coming

Coloumn A
8
6
0
0
2
7
0
0
0

Formula or VBA will delete all the "0" after 7
I have also coloumn B, C, D and E

 

[aqeelnokia99]

<style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC ; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC ; } .tableizer-table th { background-color: #104E8B ; color: #FFF ; font-weight: bold; }</style>[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]Data[/TH]
[TH]Result[/TH]
[/TR]
</thead><tbody> [TR]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=IFERROR(AGGREGATE(15,6,ROW($A$2:$A$10)/($A$2:$A$10<>0),ROWS($B$1:B1)),"")

 

[Vishaal]

It's showing error on your formula in last point "", pls help

 

[Michael M]

works fine for me, where are you putting the formula ??

 

[Vishaal]

In result i need

Data
8
6
0
0
2
7 All the zero removed

This result ok

 

[Michael M]

Ok, still works fine for me.....BUT as asked, which column are you putting the formula in?

 

[Vishaal]

In coloumn B1

 

[bosco_yip]

Maybe,

In B2, copied down :

=IF(ROW(A2)<=LOOKUP(1,0/$A$1:$A$100,ROW($A$1:$A$100)),A2,"")

Regards
Bosco

 

[Vishaal]

Its showing

The formula you typed contains an error

 

[Michael M]

never mind...self deleted