concatenate with if criteria

[nikhil0311]

AUNZ:- In discussion with PB​	CN:- ​	HK:- ​	IN:- Deal closed​	ID:- ​	JP:- ​	KR:- ​	MY:- No action required​
AUNZ:- In discussion with PB IN:- Deal closed MY:- No action required​

A1:AH is the data​

in column A6 I want the final output​

If in range A1:H1 description of country is available then only it should be shown in cell A6 else no. Here in the above example we have description available for AUSNS; IN; MY only so these should be shown as final output in cell A6. Also countries output in cell A6 should not come in the same line. it should start from new line.​

 

[jasonb75]

This will do it, remember to set wrap text on A6 or the line feed will not work.

=IF(RIGHT(A1)="-","",A1&CHAR(10))&IF(RIGHT(B1)="-","",B1&CHAR(10))&IF(RIGHT(C1)="-","",C1&CHAR(10))&IF(RIGHT(D1)="-","",D1&CHAR(10))&IF(RIGHT(E1)="-","",E1&CHAR(10))&IF(RIGHT(F1)="-","",F1&CHAR(10))&IF(RIGHT(G1)="-","",G1&CHAR(10))&IF(RIGHT(H1)="-","",H1&CHAR(10))

 

[nikhil0311]

This will do it, remember to set wrap text on A6 or the line feed will not work.

=IF(RIGHT(A1)="-","",A1&CHAR(10))&IF(RIGHT(B1)="-","",B1&CHAR(10))&IF(RIGHT(C1)="-","",C1&CHAR(10))&IF(RIGHT(D1)="-","",D1&CHAR(10))&IF(RIGHT(E1)="-","",E1&CHAR(10))&IF(RIGHT(F1)="-","",F1&CHAR(10))&IF(RIGHT(G1)="-","",G1&CHAR(10))&IF(RIGHT(H1)="-","",H1&CHAR(10))

Hi Jason. Thanks a lot for your help!
i am getting the below output as per your formula. I only want those countries where description is available. (only AUSNZ , IN & MY should come as rest of the countries don't have any description available
)

AUNZ:- In discussion with PB CN:- HK:- IN:- Deal closed ID:- JP:- KR:- MY:- No action required​

 

[nikhil0311]

Hi Jason. Thanks a lot for your help!
i am getting the below output as per your formula. I only want those countries where description is available. (only AUSNZ , IN & MY should come as rest of the countries don't have any description available
)

AUNZ:- In discussion with PB CN:- HK:- IN:- Deal closed ID:- JP:- KR:- MY:- No action required​

Hey Jason, its working absolutely fine. There were redundant spaces in my data that is why it was not working correctly. I have removed the spaces and its working like a charm.

Again thanks a lot for your help