Remove Blank and Non Data Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

In a worksheet, on sheet 2 I have survey data imported from sheet 1.
However there are some rows with "0" value and blanks in between the rows of
data. I want to display only the non blank or non zero cells.

Example of My Sheet 2:

ROW COLUMN B
1 This is my first online course
2 0
3 <blank>
4 Enjoyed the Course

Would like the data to appear like this:

ROW COLUMN B
1 This is my first online course
2 Enjoyed the Course

I tried the formula below on sheet 2 but it gave me "too few arguments for
this function" error message.

=IF(ROW()>SUMPRODUCT(--(Sheet1!AR$9:AR$5000<>0)),"",INDEX(Sheet1!AR$9:AR$5000,SMALL((Sheet1!AR$9:AR$5000<>0)*SUMPRODUCT(--ISBLANK(Sheet1!AR$9:AR$5000))+ROW())))

What other function can I use to address this problem?
Thanks for the help.
UT
 
You could add headers in row 1 and then apply data|filter|autfilter.

Filter to show the blanks and delete the visible rows.
filter to show 0's and delete those rows
remove the filter (data|filter|autofilter again)
and remove the header.
 
Unless you have a special need for that format, I would recommend that you
re-structure your data to place all information about a single entry on the
same ROW, rather than in the same COLUMN. This will make further processing
much easier.

Vaya con Dios,
Chuck, CABGx3
 
Since there's lot of data and other columns and it's a continuing survey the
data has to be in one column.

Sorting in descending order solves the problem in a way. But as the new data
comes in I don't think the sheet will preserve the sort order. Every time
sheet 1 is refreshed (sheet 1 is connected to sql server database), sheet 2
will lose the sorting.

So I am still looking for better solution.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top