Remove all rows that contain 0

[jitsuke]

I would like a formula (can't be a VBSCRIPT) that returns all rows based on column (A) not having a value of 0. The use of arrays is possible. Sample data and expected results are listed below.

Sample data:
(A) (B)
0 EX4200-48T
0 EX-PWR-320-AC
0 EX-UM-2X4SFP
0 EX-SFP-10GE-SR
56 EX4500-40F
56 EX-PWR-1200-AC
0 EX-SFP-10GE-DAC-3M
0 EX-SFP-10GE-DAC-3M
2400 EX-SFP-10GE-SR
0 QFX3500-48S4Q-ACR
0 QFX-SFP-10GE-SR

Result Expected:
56 EX4500-40F
56 EX-PWR-1200-AC
2400 EX-SFP-10GE-SR

Thanks,

E.

 

[Gary McMaster]

How about a custom autofilter? "Does not begin with" operator & "0" parameter.

Gary

 

[jitsuke]

Sadly I can't use any of the auto-filter functions due to other restrictions (3rd party integration with Excel). Only a formula will work.

 

[MrKowz]

Welcome to the forums!

Sadly, formulas cannot directly delete rows. If you cannot autofilter the data, sort the data, or run VBA, then this task will have to be done manually.

 

[jitsuke]

Is it possible to create an array that contains non zero values based on column A.

So the column A array {0,0,0,0,56,56,0,0,2400,0,0} would return {56,56,2400}

I could then add a unique identifier to each row and perform a lookup on the identifier.

Just wondering.