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

 

[etaf]

what version of office do you have 365 subscription , you could use Textjoin()

 

[kshitij_dch]

i tried =Textjoin(char(10),true,A1:Z2)

it is not skipping blanks

 

[etaf]

can you post an example of the layout, using XL2BB - see signature or on a share

ConCat - Textjoin.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	A	B	C	d	e			f	g	h
2	1	2	3		5	6	7	8		10	11
3
4
5
6
7				A B C d e f g h 1 2 3 5 6 7 8 10 11
8
Sheet2

Cell Formulas
Range	Formula
D7	D7	=TEXTJOIN(CHAR(10),1,A1:K2)

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you want.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F	G	H	I	J
2	1	2		4	5		7	8	9	10
3
4
5	A 1 B 2 D 4 E 5 G 7 H 8 I 9 J 10
Main

Cell Formulas
Range	Formula
A5	A5	=TEXTJOIN(CHAR(10),1,TRANSPOSE(IF(A2:Z2<>"",A1:Z2,"")))

 

[kshitij_dch]

Thank you Fluff for you reply ,when i used above formula in my workbook, it is showing #Value! (Value used in the formula is of wrong Data Type)

 

[Peter_SSs]

What about Fluff's suggestion...

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

.. and etaf's request

can you post an example of the layout, using XL2BB - see signature or on a share

.. otherwise you are just asking helpers to guess what the problem might be.

 

[kshitij_dch]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Singapoe	Comments	Vietnam	Comments	Hong Kong	Comments	Malaysia	Comments	India	Comments	Consolidation
2	123658	To be taken as a complete Value	123658	To be taken as a complete Value	123658		123658	To be taken as a complete Value	123658
3	25685		25685		25685		25685		25685
4	568563		568563		568563		568563		568563
5	256236		256236		256236		256236		256236
Sheet1

 

[Peter_SSs]

Thanks for updating your profile and the XL2BB sample. The sample certainly raises some comments about your original description.

• There was no mention of merged cells.
• You said A1:Z1 contained country names but it appears that is only in every second cell.
• You have numbers listed under each country name & there was no mention of those.

So the question is: What, exactly, would you want returned for that sample data?
Perhaps you could manually enter your desired result in that sheet and post it again with that result included?

 

[kshitij_dch]

Hello Peter , Apologies for not explaining the data in a structured manner , please find below snapshot , Consolidated Comments in Column K that's how i want

Book1
	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 complete Value Vietnam Comments Not to be taken as complete Value Malaysia Comments Values are same
3	25685				25685		123658		25685
4	568563		568563		568563		123658		568563
5	256236				256236		123658		256236
Sheet1