Return All Non-Blank Cells

[shorticake]

I have the following text in column A rows 17 through 26

17 PY02
18
19 PY04
20 PY05
21 PY06
22 PY07
23 PY08
24 PY09
25 PY10
26 PY11

Then I use this formula
=IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMALL(IF($A$26<>"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"")
in A43 and copy down to A52.

I'm expecting it to return the following in Column A rows 43:52

43 PY02
44 PY04
45 PY05
46 PY06
47 PY07
48 PY08
49 PY09
50 PY10
51 PY11
52

But I'm getting this instead:

43 PY02
44 PY05
45 PY06
46 PY08
47 PY10
48
49
50
51
52

I don't know why it's skipping PY04, PY07, PY09, and PY11.

Any help is greatly appreciated!

 

[Ron Coderre]

Try something like this:

A43:
=IF(COUNTA($A$17:$A$26)>=ROWS($43:43),INDEX($A$17:$A$26,SMALL((ISBLANK($A$17:$A$26)*10^99+ROW($A$17:$A$26)-ROW($A$17)+1),ROWS($43:43))),"")

Copy that formula down through A52

Note: in case text wrap impacts the display, there are NO spaces in that
formula.

Using your sample data, that formula returns these values in A43:A52
PY02
PY04
PY05
PY06
PY07
PY08
PY09
PY10
PY11
(blank)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ron Coderre]

The formula I posted needed one more tweak....

Try this regular formula:
=IF(COUNTA($A$17:$A$26)>=ROWS($43:43),INDEX($A$17:$A$26,SMALL(INDEX((ISBLANK($A$17:$A$26)*10^99+ROW($A$17:$A$26)-ROW($A$17)+1),0),ROWS($43:43))),"")

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[T. Valko]

Try it like this (array entered):

=IF(ROWS(A$43:A43)<=COUNTA(A$17:A$26),INDEX(A$17:A$26,SMALL(IF(A$17:A$26<>"",ROW(A$17:A$26)-MIN(ROW(A$17))+1),ROWS(A$43:A43))),"")

 

[shorticake]

Thanks so much!!

Try it like this (array entered):

=IF(ROWS(A$43:A43)<=COUNTA(A$17:A$26),INDEX(A$17:A$26,SMALL(IF(A$17:A$26<>"",ROW(A$17:A$26)-MIN(ROW(A$17))+1),ROWS(A$43:A43))),"")

 

[shorticake]

I wanted to follow the logic so I tried to run Evaluate Function, but each
time it immediately shutdown excel. Do you know why this is happening?

Thanks again!

 

[T. Valko]

I don't know the specific reason so I just call it a "bug".

I've complained about this happening several times! I know this happens in
Excel 2002 and Excel 2003. The evaulute formula tool was introduced in Excel
2002. I don't know if this is still available in Excel 2007. It's a very
useful tool (when it's not crashing Excel!).

It only happens on cetain types of array formulas and usually happens when
the process gets to this type of expression:

SMALL(IF(A$17:A$26<>""

I've learned to recognize what types of formulas will cause this crash so I
just avoid evaluating them altogether or, I'll use a minimal range size (no
more than 5 cells) if I just need to verify the logic is working correctly.