If condition NE Next line

Occasional Contributor

Team,

 

I am having two worksheet , 

Sheet 1 

Sheet 2

 

Sheet 1 is having following data,

 

Micro440_0-1637608864945.png

 

Now all the time Column "amount" yellow highlighted i have to filter it to exclude the 0 value line to copy the remaining line where column value not equal to 0

 

i am trying following formula in next sheet which is sheet1,

 

with if condition is it possible show only line if column(Amount) value not equal to 0.

 

expected

 

Micro440_1-1637609224708.png

 

 

4 Replies

@Micro440 

 

Hi Micro, if your data in sheet 1 is in a table, you could try using the FILTER() function.

 

Assuming your table is named "Table1", this formula will filter your table for any rows where Amount is greater or less than 0.

=+FILTER(Table1,Table1[Amount]<>0)

 

If your data isn't in a table, press  Ctrl + L to turn it into one, or you can use the same method above but use ranges instead like so:

=+FILTER(A:J,J:J<>0)

 

 

Thanks for your quick reply. Understood about table creation in sheet1. I have done it now. But i am unable to connect with filter formula.

Should use this in sheet2 ? please advise.
Experts please share your ideas to solve the same .

@Micro440 ,

 

yes, use the formula that @DKoontz has provided in the sheet2. But please note, the FILTER function is available only in Excel from Microsoft 365 or Excel 2021. If you use Excel 2019 or older, FILTER function is not available.