returning nonblank cells... procedure

woodzilla

New Member
Joined
Jun 6, 2002
Messages
17
In the book, Excel 2000 formulas, by Walkenbach. I've been trying to use the returning nonblank cells from a range formula on page 354. Even when I use the sample file, I have not been able to reproduce the results from the sample. At best, I get the top number from the source column repeated.
I must be missing a procedure somewhere. So If someone that has used this formula, can explain the step by step procedure, many thanks by me.
I've looked in the search for prior posts for info on this, as well as other websites and books. Just too much missing information for me to set this up properly. And I don't understand the complex formula, other than changing the name of the source array.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-03 22:34, woodzilla wrote:
In the book, Excel 2000 formulas, by Walkenbach. I've been trying to use the returning nonblank cells from a range formula on page 354. Even when I use the sample file, I have not been able to reproduce the results from the sample. At best, I get the top number from the source column repeated.
I must be missing a procedure somewhere. So If someone that has used this formula, can explain the step by step procedure, many thanks by me.
I've looked in the search for prior posts for info on this, as well as other websites and books. Just too much missing information for me to set this up properly. And I don't understand the complex formula, other than changing the name of the source array.

You probably need just to enter the formula in a cell then select a range from this cell on as big as data range. After having selected the range, you need to hit control+shift+enter at the same time.
 

woodzilla

New Member
Joined
Jun 6, 2002
Messages
17
After toying with the book example, its clear its makes a difference on how the formula is copied into the range of cells. To copy the formula in one cell and paste it into a range of cells does not work. To copy a range of cells that already have the formulas in all of them, and paste them into a nearby range of cells does work.
What is the proper way of entering a formula into a range of cells by copy and paste?
I understand one needs to select a range of cells, then enter the formula in the formula bar. But I can't seem to copy a formula into the formula bar. This is a very long formula.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-04 16:34, woodzilla wrote:
After toying with the book example, its clear its makes a difference on how the formula is copied into the range of cells. To copy the formula in one cell and paste it into a range of cells does not work. To copy a range of cells that already have the formulas in all of them, and paste them into a nearby range of cells does work.
What is the proper way of entering a formula into a range of cells by copy and paste?
I understand one needs to select a range of cells, then enter the formula in the formula bar. But I can't seem to copy a formula into the formula bar. This is a very long formula.

There is a big difference between an array-formula that returns a scalar/single cell result and one that returns an array-constant.

Enclose that array-formula and enclose it with INDEX...

=INDEX(the-target-array-formula,ROW(1:1))

Array-enter this in a single cell and copy down as you would copy an odinary formula...

You'll see the INDEX function to pull off the lements of the computed array constant one by one...
 

woodzilla

New Member
Joined
Jun 6, 2002
Messages
17
After much fooling around, I figured out how to use the RETURNING NONBLANK CELLS FROM A RANGE, from page 354 of the Excel 2000 Formulas book. So for all other readers of this message, here's what I did.

The problem, I have dozens of columns of data, each with 100s of rows of numbers, over half that are blank cells. I wanted to summarize the columns of numbers so there is no blanks, and updates dynamically.

Select each column of source data cells,name each one,(from menu, select insert-name-define).

Copy or type in the original formula into wordpad. Copy that as many times as you have named source data columns. Then you need to go into each formula and replace the word "data" with the name of your own named data column, in 7 different places within the formula.

In Excel, select a new destination column, in my case, 300 rows,(can be on a different sheet) copy and paste the modified formula from wordpad into the formula bar, not a cell. Hit shift+ctrl+enter to send the new formula into the new array.

The new list of numbers without blanks, should pop up in a few seconds.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-05 11:38, woodzilla wrote:
After much fooling around, I figured out how to use the RETURNING NONBLANK CELLS FROM A RANGE, from page 354 of the Excel 2000 Formulas book. So for all other readers of this message, here's what I did.

The problem, I have dozens of columns of data, each with 100s of rows of numbers, over half that are blank cells. I wanted to summarize the columns of numbers so there is no blanks, and updates dynamically.

Select each column of source data cells,name each one,(from menu, select insert-name-define).

Copy or type in the original formula into wordpad. Copy that as many times as you have named source data columns. Then you need to go into each formula and replace the word "data" with the name of your own named data column, in 7 different places within the formula.

In Excel, select a new destination column, in my case, 300 rows,(can be on a different sheet) copy and paste the modified formula from wordpad into the formula bar, not a cell. Hit shift+ctrl+enter to send the new formula into the new array.

The new list of numbers without blanks, should pop up in a few seconds.

Wordpad? No need for that... Just put the Walkenbach formula in the first cell of the destination column, edit the formula for ranges in that cell by substituting defined names then select the range as big as the source range from the initial cell on [it seems you find this step difficult to apply] and hit control+shift+enter.

By the way, since you're applying this formula to a dozen columns times ~300 rows, you'll notice a considerable performance degredation.

Here is another suggestion to counteract a bit the performance problems.

Download and install the morefunc.xll add-in from http://longre.free.fr/english/index.html.

The figure below shows the relevant computations...

BigNum is defined as follows:

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum as name in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

The essential array-formula is:

=IF(ISNUMBER(SETV(SMALL(IF(Data<>"",INTVECTOR($E$3,1,0,1)),INTVECTOR($E$3,1,0,1)))),INDEX(Data,GETV()),"")
multi-cell array formulas.xls
ABCDEFG
1Returnonlynon-blankcellsfromarange
2
320
4DataNon-BlanksNon-Blanks
5333333
6-33-33-33
7444444
8-5-5
9-500
1004343
1143-2-2
12-3-3
13-2-33-33
14-355
15-3399
161111
1751212
18  
19  
20  
219  
22  
2311  
2412  
Sheet2
 

Forum statistics

Threads
1,144,359
Messages
5,723,893
Members
422,524
Latest member
wirkkarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top