# returning nonblank cells... procedure

#### woodzilla

##### New Member
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.

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.

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...

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.

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.

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

Replies
2
Views
608
Replies
3
Views
123
Replies
1
Views
295
Replies
3
Views
263
Replies
2
Views
139

1,219,688
Messages
6,149,725
Members
450,910
Latest member
DianeG

### 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.

### Which adblocker are you using?

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

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