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.

UT wrote:

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

--

Dave Peterson

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



"UT" wrote:

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

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

"CLR" wrote:

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



"UT" wrote:

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