how to return non empty cells over several columns

[Guest]

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.

 

[robert111]

using =large(range,1) will display the largest number

=large(range,23) will display the 23rd largest number

do the numbers have to be in the order they occur?

 

[Guest]

No, not necessarily, however, all of them do have to appear. I think this may
answer my question, in that I can just use the large function several times
and have them displayed in order. Many thanks.

 

[Ron Rosenfeld]

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

 

[Guest]

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.