Hope someone can help me with the formula below.

=IF(AU20,(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)&(I F(Criteria!$E$9="Y",IF(Selection!AF2="Y","","Inter net"),0))&CHAR(10)&(IF(Criteria!$E$10="Y",IF(Selec tion!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(S election!AI2="Y","","Trsf.
to
office"),0))&CHAR(10)&(IF(Criteria!$E$13="Y",IF(Se lection!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(Se lection!AM2="Y","","No of
BOD"),0))&CHAR(10)&(IF(Criteria!$E$16="Y",IF(Selec tion!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.

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.
--
Gary''s Student - gsnu200909


"Kim" wrote:

Hope someone can help me with the formula below.

=IF(AU20,(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)&(I F(Criteria!$E$9="Y",IF(Selection!AF2="Y","","Inter net"),0))&CHAR(10)&(IF(Criteria!$E$10="Y",IF(Selec tion!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(S election!AI2="Y","","Trsf.
to
office"),0))&CHAR(10)&(IF(Criteria!$E$13="Y",IF(Se lection!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(Se lection!AM2="Y","","No of
BOD"),0))&CHAR(10)&(IF(Criteria!$E$16="Y",IF(Selec tion!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.

It still come up the same without removing the 0.

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200909


"Kim" wrote:

Hope someone can help me with the formula below.

=IF(AU20,(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)&(I F(Criteria!$E$9="Y",IF(Selection!AF2="Y","","Inter net"),0))&CHAR(10)&(IF(Criteria!$E$10="Y",IF(Selec tion!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(S election!AI2="Y","","Trsf.
to
office"),0))&CHAR(10)&(IF(Criteria!$E$13="Y",IF(Se lection!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(Se lection!AM2="Y","","No of
BOD"),0))&CHAR(10)&(IF(Criteria!$E$16="Y",IF(Selec tion!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.

Be sure to follow the instruction on the array formula entry!
--
Gary''s Student - gsnu200909


"Kim" wrote:

It still come up the same without removing the 0.

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200909


"Kim" wrote:

Hope someone can help me with the formula below.

=IF(AU20,(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)&(I F(Criteria!$E$9="Y",IF(Selection!AF2="Y","","Inter net"),0))&CHAR(10)&(IF(Criteria!$E$10="Y",IF(Selec tion!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(S election!AI2="Y","","Trsf.
to
office"),0))&CHAR(10)&(IF(Criteria!$E$13="Y",IF(Se lection!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(Se lection!AM2="Y","","No of
BOD"),0))&CHAR(10)&(IF(Criteria!$E$16="Y",IF(Selec tion!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.

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

=IF(AU110,(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",I F(Selection!AG11="Y","","Tax"&CHAR(10)),""))&(IF(C riteria!$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(S election!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(S election!AM11="Y","","No of
BOD"&CHAR(10)),""))&(IF(Criteria!$E$16="Y",IF(Sele ction!V11="Y","","City
Cap"),"")),"")


"Gary''s Student" wrote:

Be sure to follow the instruction on the array formula entry!
--
Gary''s Student - gsnu200909


"Kim" wrote:

It still come up the same without removing the 0.

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200909


"Kim" wrote:

Hope someone can help me with the formula below.

=IF(AU20,(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)&(I F(Criteria!$E$9="Y",IF(Selection!AF2="Y","","Inter net"),0))&CHAR(10)&(IF(Criteria!$E$10="Y",IF(Selec tion!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(S election!AI2="Y","","Trsf.
to
office"),0))&CHAR(10)&(IF(Criteria!$E$13="Y",IF(Se lection!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(Se lection!AM2="Y","","No of
BOD"),0))&CHAR(10)&(IF(Criteria!$E$16="Y",IF(Selec tion!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.