Complex formula help

[Kim]

Hope someone can help me with the formula below.

=IF(AU2>0,(IF(Criteria!$E$7="Y",IF(Selection!AD2="Y","","LRA"),0))&CHAR(10)&(IF(Criteria!$E$8="Y",IF(Selection!AE2="Y","","Breakfast"),0))&CHAR(10)&(IF(Criteria!$E$9="Y",IF(Selection!AF2="Y","","Internet"),0))&CHAR(10)&(IF(Criteria!$E$10="Y",IF(Selection!AG2="Y","","Tax"),0))&CHAR(10)&(IF(Criteria!$E$11="Y",IF(Selection!AH2="Y","","Free
Parking"),0))&CHAR(10)&(IF(Criteria!$E$12="Y",IF(Selection!AI2="Y","","Trsf.
to
office"),0))&CHAR(10)&(IF(Criteria!$E$13="Y",IF(Selection!AJ2="Y","","Airport
Transfer"),0))&CHAR(10)&(IF(Criteria!$E$14="Y",IF(Selection!AN2="Y","","Cancellation
Policy"),0))&CHAR(10)&(IF(Criteria!$E$15="Y",IF(Selection!AM2="Y","","No of
BOD"),0))&CHAR(10)&(IF(Criteria!$E$16="Y",IF(Selection!V2="Y","","City
Cap"),0)),"").

The current result it gives me are

LRA
0
0
0
0
0
0
0
0
City Cap

Is there a way whre I can get the formulas just to ignore all the 0 and
return the text value ie like below.

LRA
City Cap

Thanks.

 

[Gary''s Student]

Your current formula is already complex!
Lets say you have used column A and the displayed data is some non-zero
values separated by blocks of zeros and we want to "squeeze out" the zero
values.

In B1 enter the following array formula and copy down:

=IF(ROWS($1:1)<=COUNTA($A$1:$A$100),INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>0,ROW($A$1:$A$100)-MIN(ROW($A$1:$A$100))+1),ROWS($1:1))),"")


When you see the error you have copied far enough.

This is an array formula that must be enter with CNTRL-SHFT-ENTER rather
than just the ENTER key.

 

[Kim]

It still come up the same without removing the 0.

 

[Gary''s Student]

Be sure to follow the instruction on the array formula entry!

 

[Kim]

Yes I use the array formulas. But now I change the formulas slightly and it
kind of work.

=IF(AU11>0,(IF(Criteria!$E$7="Y",IF(Selection!AD11="Y","","LRA"&CHAR(10)),""))&(IF(Criteria!$E$8="Y",IF(Selection!AE11="Y","","Breakfast"&CHAR(10)),""))&(IF(Criteria!$E$9="Y",IF(Selection!AF11="Y","","Internet"&CHAR(10)),""))&(IF(Criteria!$E$10="Y",IF(Selection!AG11="Y","","Tax"&CHAR(10)),""))&(IF(Criteria!$E$11="Y",IF(Selection!AH11="Y","","Free
Parking"&CHAR(10)),""))&(IF(Criteria!$E$12="Y",IF(Selection!AI11="Y","","Trsf.
to
office"&CHAR(10)),""))&(IF(Criteria!$E$13="Y",IF(Selection!AJ11="Y","","Airport
Transfer"&CHAR(10)),""))&(IF(Criteria!$E$14="Y",IF(Selection!AN11="Y","","Cancellation
Policy"&CHAR(10)),""))&(IF(Criteria!$E$15="Y",IF(Selection!AM11="Y","","No of
BOD"&CHAR(10)),""))&(IF(Criteria!$E$16="Y",IF(Selection!V11="Y","","City
Cap"),"")),"")