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

 

[Vishaal]

I have paste the formula in B2
Its only showing #NAME ?

 

[aqeelnokia99]

put formula in b2 and your working colom is A

 

[Vishaal]

Yes

I am doing the same

MS office 2007
Window 7
Same data as showing in first post

But not working

Can u pls upload the working file, so may i know the where is mistakr

 

[aqeelnokia99]

my be offcie 2007 not suport AGGREGATE function wait i try to make a arry formula for u

 

[aqeelnokia99]

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

 

[aqeelnokia99]

REQ CONTROL + **** + enter
=IFERROR(INDEX($A$1:$A$10,SMALL(IF($A$2:$A$10<>0=TRUE,ROW($A$2:$A$10),""),ROWS($B$1:B1))),"")

 

[Vishaal]

Ok checking

Thanks for your support

 

[Vishaal]

Ok

But its have removed all the "0" but i want only remove the "0" after 7 in coloumn A

 

[aqeelnokia99]

pllease share more your data and what u want ?? u want to remve only 0 after 7 and u have multipul 7 in colom a and how maany 0 are after 7 in colom A please describe in details

 

[Vishaal]

I have not multiple 7

Its a data

Coloumn A
8
6
0
0
2
7
0
0
0

I want the following result

Coloumn A
8
6
0
0
2
7

MS Office 2007
Window 7 (32bit)

In coloumns only 30 values including 0

Some other example

Data
7
5
0
0
3
0
9
8
6
0
0
0

Result will be

Data
7
5
0
0
3
0
9
8
6

I hope every thing is clear for you

You can also said that when "0" start in continuation, remove theme