Hi,

I have a similar problem. On sheet 2 I have data imported from sheet 1.
However there are 0 and blanks in between the rows of data. I want to display
only the non blank or non zero cells.

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())))

Thanks for the help.

"Ron Rosenfeld" wrote:

On Thu, 30 Mar 2006 03:38:03 -0800, iago
wrote:

Hi, this might be difficult for me to explain, so I'll use an example.
I have several columns that look like this:

A B C
132 0 0
234 0 0
456 0 0
0 678 0
0 890 0
0 0 275

They are the result of an if import. I want another worksheet to display A B
and C With their numbers right at the top, and no zeroes to follow.
Essentially, I think I want to display all non empty cells (like the filter
function), but for several columns, all displaying their non-zeroes at the
top. Let me know if this is unclear.

One way, if sorting is acceptable, would be to

Edit/Copy Paste Special Values from Sheet1 to Sheet2

Then sort each column individually in Descending order.

========================
If you want to retain the same order as in the original, and have the result be
dynamic, then, with your data in Sheet1!A1:C10:

Sheet2!A1:

=IF(ROW()SUMPRODUCT(--(Sheet1!A$1:A$10<0)),"",
INDEX(Sheet1!A$1:A$10,SMALL((Sheet1!A$1:A$10<0)*
ROW(Sheet1!A$1:A$10),COUNTIF(Sheet1!A$1:A$10,0)+
SUMPRODUCT(--ISBLANK(Sheet1!A$1:A$10))+ROW())))

Change the "A$10" term to reflect the extent of your actual range. Blank rows
are acceptable to be included in the range.

This formula must be **array-entered**. After you type or paste the formula
into the cell, confirm it by holding down <ctrl<shift while you hit <enter.
Excel will place braces {...} around the formula.

After you have entered the formula, you can select it and copy/drag down as far
as needed; and then select that column and copy drag to the right to column C.


--ron