Array formula excluding #N/A

[Greasle]

Hi all,

I'm looking for a array formula doing this:

exclude #N/A from a list
but keep blanco

( i'm not a coder but i code. mostly i find what i need using google only. this time... i think my search string is to poor.)

Thx Greasle

 

[Peter_SSs]

Not enough information about what you have and what you want to achieve.

Possible to post some sample data and expected results together with an explanation?

 

[Greasle]

Lets say its all text including the "#N/A"

 

[Greasle]

The picture is not clear... ??

Ok I try... i'm deslectic...

in column B is a big list that i want to print. And its dynamic. its over 1000 cells/lines but 85% is filled with "#N/A" I like to print all others except "#N/A". But I dont want "#N/A" replaced by a blanco. So what i have is in Column B what I want is in Column D.

 

[Peter_SSs]

The picture is not clear... ??

No, there is no picture. My signature block below has suggestions for posting small screen shots (10-20 rows, not 1000 )

in column B is a big list that i want to print. And its dynamic. its over 1000 cells/lines but 85% is filled with "#N/A" I like to print all others except "#N/A". But I dont want "#N/A" replaced by a blanco.

Why don't you put an AutoFilter on, use the AutoFilter to exclude the #N/A values, print, then use the AutoFilter to re-show all rows?

 

[Greasle]

http://www.cloudswain.byethost7.com/Boats/array.png

no picture? here is a picture in my browser. Bug ?

A1:H20 is what i did.

 

[Peter_SSs]

The picture eventually showed up for me. I don't know why it didn't to start with.

Anyway, since you want to do this to print, did you consider my suggestion in post #5 ?

If you really do want to do it by formula, for the layout shown in the sample, try this formula in D3 and copy down
<del>=IFERROR(INDEX(B$3:B18,AGGREGATE(15,6,(ROW(B$3:B$18)-ROW(B$3)+1)/ISTEXT(B$3:B$18),ROWS(D$3:D3))),"")</del>


Edit: Sorry, the above formula was missing a $ sign and also I forgot about including blanks. Try this instead.
=IFERROR(INDEX(B$3:B$18&"",AGGREGATE(15,6,(ROW(B$3:B$18)-ROW(B$3)+1)/ISTEXT(B$3:B$18&""),ROWS(D$3:D3))),"")

 

[Greasle]

Nice Peter, !!!

that formula worked great in my example.

I'm going to work with this.

Yes i tried to figger out how to do it using autofiller. No luck.. i simply don't understand.

Thank you Peter.

Greasle

 

[Peter_SSs]

OK, good news. Thanks for letting us know.

 

[Greasle]

Np it did not work in the real sheet....

it looks so simple... cant believe is so hard.

I go to find another way... got the feeling this leads in a maze... thx for your time Peter.