Concatenate if cell in a range is not blank

[kshitij_dch]

Hello all,

i am working on a file where i have data from A1 to Z1 ( Country name) and from A2 to Z2 ( Comments)

1st row has country name
2nd row has comments

i am looking to concatenate comments and country name , i am ok to do it with normal concatenate however i have few cells in comments which are blank

i want formula only to concatenate country name and comments where comments are present in cells , if cell is blank formula should skip blanks

i am ok with VBA code if possible

 

[Peter_SSs]

With Excel 365, try this.

21 02 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Singapore	Singapore Comments	Vietnam	Vietnam Comments	Hong Kong	Hong Kong Comments	Malaysia	Malaysia Comments	India	India Comments	Consolidation
2	123658	To be taken as a complete Value	123658	Not To be taken as a complete Value	123658		123658	Values are same	123658		Singapore Comments To be taken as a complete Value Vietnam Comments Not To be taken as a complete Value Malaysia Comments Values are same
3	25685				25685		123658		25685
4	568563		568563		568563		123658		568563
5	256236				256236		123658		256236
Textjoin

Cell Formulas
Range	Formula
K2	K2	=TEXTJOIN(CHAR(10)&CHAR(10),1,IF(RIGHT(B1:J1,8)="Comments",IF(B2:J2<>"",B1:J1&CHAR(10)&B2:J2,""),""))