software

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.


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?--
robert111
------------------------------------------------------------------------
robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=528040No, 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.

quot;robert111quot; wrote:

gt;
gt; using =large(range,1) will display the largest number
gt;
gt; =large(range,23) will display the 23rd largest number
gt;
gt; do the numbers have to be in the order they occur?
gt;
gt;
gt; --
gt; robert111
gt; ------------------------------------------------------------------------
gt; robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
gt; View this thread: www.excelforum.com/showthread...hreadid=528040
gt;
gt;

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

gt;Hi, this might be difficult for me to explain, so I'll use an example.
gt;I have several columns that look like this:
gt;
gt;A B C
gt;132 0 0
gt;234 0 0
gt;456 0 0
gt;0 678 0
gt;0 890 0
gt;0 0 275
gt;
gt;They are the result of an if import. I want another worksheet to display A B
gt;and C With their numbers right at the top, and no zeroes to follow.
gt;Essentially, I think I want to display all non empty cells (like the filter
gt;function), but for several columns, all displaying their non-zeroes at the
gt;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()gt;SUMPRODUCT(--(Sheet1!A$1:A$10lt;gt;0)),quot;quot;,
INDEX(Sheet1!A$1:A$10,SMALL((Sheet1!A$1:A$10lt;gt;0)*
ROW(Sheet1!A$1:A$10),COUNTIF(Sheet1!A$1:A$10,0)
SUMPRODUCT(--ISBLANK(Sheet1!A$1:A$10)) ROW())))

Change the quot;A$10quot; 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 lt;ctrlgt;lt;shiftgt; while you hit lt;entergt;.
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

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 quot;too few arguments for
this functionquot; error message.

=IF(ROW()gt;SUMPRODUCT(--(Sheet1!AR$9:AR$5000lt;gt;0)),quot;quot;,INDEX(Sheet1!AR$9:AR$ 5000,SMALL((Sheet1!AR$9:AR$5000lt;gt;0)*SUMPRODUCT(--ISBLANK(Sheet1!AR$9:AR$5000)) ROW())))

Thanks for the help.

quot;Ron Rosenfeldquot; wrote:

gt; On Thu, 30 Mar 2006 03:38:03 -0800, iago gt;
gt; wrote:
gt;
gt; gt;Hi, this might be difficult for me to explain, so I'll use an example.
gt; gt;I have several columns that look like this:
gt; gt;
gt; gt;A B C
gt; gt;132 0 0
gt; gt;234 0 0
gt; gt;456 0 0
gt; gt;0 678 0
gt; gt;0 890 0
gt; gt;0 0 275
gt; gt;
gt; gt;They are the result of an if import. I want another worksheet to display A B
gt; gt;and C With their numbers right at the top, and no zeroes to follow.
gt; gt;Essentially, I think I want to display all non empty cells (like the filter
gt; gt;function), but for several columns, all displaying their non-zeroes at the
gt; gt;top. Let me know if this is unclear.
gt;
gt; One way, if sorting is acceptable, would be to
gt;
gt; Edit/Copy Paste Special Values from Sheet1 to Sheet2
gt;
gt; Then sort each column individually in Descending order.
gt;
gt; ========================
gt; If you want to retain the same order as in the original, and have the result be
gt; dynamic, then, with your data in Sheet1!A1:C10:
gt;
gt; Sheet2!A1:
gt;
gt; =IF(ROW()gt;SUMPRODUCT(--(Sheet1!A$1:A$10lt;gt;0)),quot;quot;,
gt; INDEX(Sheet1!A$1:A$10,SMALL((Sheet1!A$1:A$10lt;gt;0)*
gt; ROW(Sheet1!A$1:A$10),COUNTIF(Sheet1!A$1:A$10,0)
gt; SUMPRODUCT(--ISBLANK(Sheet1!A$1:A$10)) ROW())))
gt;
gt; Change the quot;A$10quot; term to reflect the extent of your actual range. Blank rows
gt; are acceptable to be included in the range.
gt;
gt; This formula must be **array-entered**. After you type or paste the formula
gt; into the cell, confirm it by holding down lt;ctrlgt;lt;shiftgt; while you hit lt;entergt;.
gt; Excel will place braces {...} around the formula.
gt;
gt; After you have entered the formula, you can select it and copy/drag down as far
gt; as needed; and then select that column and copy drag to the right to column C.
gt;
gt;
gt; --ron
gt;