Help! How to remove rows of zeros to shrink the data to an organized list?

**unpluggedmusic** · 10-11-2012, 07:49 AM

Dear all,

I have a task that's been bothering my work for a long time. I have attached a fictatious list to seek for help from the experts here.
I have a 2000+ list of names that i do not need to sort. I just need to remove the rows of zeros that is taking up space on the datasheet and making reading difficult.

I need the rows of zeros to be removed and display the nett amount of information without sorting. I also need to have the end results on a different sheet, which i believe will complicate the formula further.

I need a formula for this. Please do help! Thanks guys!

Lim

**Ace_XL** · 10-11-2012, 07:52 AM

Use auto filters..

- Select columns A-C Apply autofilter
- Filter on zero in each column.
- Select rows..right click ..delete row

**Pete_UK** · 10-11-2012, 07:58 AM

The easiest way is to use Autofilter. In your example you could put this formula in E2:

=IF(COUNTIF(A2:C2,"0")=3,"hide","")

then copy down to the bottom of your data. Then apply autofilter to column E and on the filter pull-down de-select Hide then your rows will be bunched up as you want them. If you want to get back to the original list you can Select All on the filter pull-down.

Hope this helps.

Pete

**Fotis1991** · 10-11-2012, 08:35 AM

Hi

In G2, put this ARRAY(Confirm, using Control+Shift+Enter. Not Just Enter), formula.

=IFERROR(INDEX(A$2:A$20,SMALL(IF(A$2:A$20<>0,ROW(A$2:A$20)-1),ROW(A1))),"")

Copy down and across(to the right).