Thread: Ignoring blanks with array formula?

Heyyo,

I'm currently using an array formula to search through a table for multiple instances of one criterion and return text from another column:

PHP Code: 

{=IFERROR(INDEX(GrandTab,SMALL(IF(COUNTIF($C$4,INDEX(GrandTab,,1,1))>0,ROW(GrandTab)-MIN(ROW(GrandTab))+1),ROW(A7)),10),"")} 


Where C4 is my criterion, and it looks through GrandTab to find all matching data, then returns lists of text from column 10. My problem is that many cells matching C4 have blank values in column 10, and I need to skip over those, so I get a solid list of text returns back from column 10 without a load of blanks in between.

I've seen "<>" included to do this somehow, but I'm not sure how it works and where to use it.

Any help you guys can offer would be greatly appreciated, thanks!

Try:

=IFERROR(INDEX(GrandTab,SMALL(IF(COUNTIF(INDEX(GrandTab,,1,1),$C$4)>0,IF(INDEX(GrandTab,,10,1)<>"",ROW(GrandTab)-MIN(ROW(GrandTab))+1)),ROW(A7)),10),"")

by the way, it looked like you had the arguments in the COUNTIF reversed.... I switched them....

Hiya NBVC,

Firstly thank you much for your reply!

I didn't think I had my COUNTIF arguments reversed but I'm still pretty new to array formulae, however when I do switch em like yours it's returning the wrong data :S If I leave it the way it is but use your code, it's still returning blank cells. I put a couple screenshots in:

http://i.imgur.com/vzEwj.png - Array (blank returns still appearing)
http://i.imgur.com/PiMHo.png - Data

*EDIT* I just checked the blanks, they've actually got some spaces in! Sneaky, that must be it!

Thanks again

I removed all the blanks and it's working perfectly, thank you! Sorry to be cheeky, but whilst I'm here, is there also a simple way of eliminating repeat entries as well? I've been trying to work in the FREQUENCY function but again, I'm not sure how to form it.

Much love