Array to clear Blanks - formula analysis and possible modification

[tomcatonnet99]

Hi, I'm trying to evaluate the array formula to demonstrate its working... Also interested in creating one that might work without the use of arrays..?

Formula being evaluated ....


'=IF(ROW()-ROW(INDIRECT(NoBlanksRange,TRUE))+1>ROWS(INDIRECT(BlanksRange,TRUE))-COUNTBLANK(INDIRECT(BlanksRange,TRUE)),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT(BlanksRange,TRUE)<>"",ROW(INDIRECT(BlanksRange,TRUE)),ROW()+ROWS(INDIRECT(BlanksRange,TRUE)))),ROW()-ROW(INDIRECT(NoBlanksRange,TRUE))+1),COLUMN(INDIRECT(BlanksRange,TRUE)),4)))' and ofcourse this one has been Ctrl+shft entered....

 

[Hui]

What are the formulas or ranges for the 2 Named Ranges:

NoBlanksRange

BlanksRange


also


What is it your trying to achieve with the formula ?


If I put

NoBlanksRange as a1

BlanksRange as B1


If I run it on this data it works

[pre]

c1:c10	d1:d10	a	8	8
a	7	7
a		3
a	3	4
a	4	5
a	5	6
a	6	7
a		8
a	7
a	8
Except if I delete the a's it works just the same


c1:c10	d1:d10		8	8
7	7
3
3	4
4	5
5	6
6	7
8
7
8

[/pre]
So i don't know what the NoBlanksRange is meant to do?

 

[tomcatonnet99]

Hi Hui, Many thanks for taking the time out for this and its just that in a forum thats got no way of attaching files, images making it that much more frustrating and my not wanting to carry on in here but for your efforts thats made me continue... thank you.


Well, I've broken down the formula in smaller parts and as such I suppose its the smaller parts without being entered as array formulae thats probably the cause...


'Blanks row()-row(noblanks)+1 rows(blank)-countblank(blanks) value for small k SMALL ADDRESS Indirect of address

Apples 1 6 2 1 2 A2 Apples

2 6 17 2 #NUM! #NUM! #NUM!

3 6 18 3 #NUM! #NUM! #NUM!

4 6 19 4 #NUM! #NUM! #NUM!

Fijoas 5 6 2 5 #NUM! #NUM! #NUM!

6 6 21 6 #NUM! #NUM! #NUM!

Kiwifruit 7 6 2 7 #NUM! #NUM! #NUM!

8 6 23 8 #NUM! #NUM! #NUM!

9 6 24 9 #NUM! #NUM! #NUM!

10 6 25 10 #NUM! #NUM! #NUM!

Plums 11 6 2 11 #NUM! #NUM! #NUM!

Kiwifruit 12 6 2 12 #NUM! #NUM! #NUM!

13 6 28 13 #NUM! #NUM! #NUM!

Cherries 14 6 2 14 #NUM! #NUM! #NUM!


In anycase I'm gradually getting to a point of making the shorter version work (but for a few glitches when it comes to assigning the right 'k' for the 'Small' function...

with this formula...


=INDIRECT(ADDRESS(SMALL(IF(A2:A$15<>"",ROW(A2:A$15),ROW()+ROWS($A$2:A$15)),ROW()-1),1,4),TRUE) What needs correction is a proper 'k' value to the small ....


'Blanks MyNoBlanks


Apples Apples

Kiwifruit

Plums

Kiwifruit

Fijoas Cherries

0

Kiwifruit0

#NUM!

#NUM!

#NUM!

Plums #NUM!

Kiwifruit#NUM!

#NUM!

Cherries#NUM!


'

'


Cheers!

 

[tomcatonnet99]

Anyways all sorted .... for now


{=INDEX(A:A,SMALL(IF(BlanksRange<>"",ROW(BlanksRange),""),ROW(A1)),1)}