CONCATENATE function

[shuklaankur281190]

Hi All,

There is a problem i am facing with CONCATENATE function. Here is in below table.

Harry	AC
	DA
	PC
	KA
Stu	DA
	KA
Neena	PC
Pihu	KA
	NA
	RE
	PK
Kenny	NE
	RA
	TE

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

I wanna add add name and his character showing in front of till second name in C column.

Please help me

 

[Rick Rothstein]

have been playing with this for couple of hour this formula seems to work upto a limit of four instances of column B

it can be extended to include more but my coffee ran out

so stopped at your data sample

=IF(A2<>"",IF(A3<>"",CONCATENATE(A2," ",B2),IF(A4<>"",CONCATENATE(A2," ",B2," ",B3),IF(A5<>"",CONCATENATE(A2," ",B2," ",B3," ",B4),IF(A6<>"",CONCATENATE(A2," ",B2," ",B3," ",B4," ",B5),CONCATENATE(A2," ",B2))))),"")

That is a good idea you have there as long as the OP knows the maximum number of characters that a name can have and as long as that maximum is reasonably small. Two things about your formula, though... first, the delimiter between the characters should be a comma, the space is only suppose to be between the name and first character... second, my preference is not to use the CONCATENATE function, rather using the ampersand to join the text strings together. Here is your formula with both those modifications done to it...

=IF(A2<>"",IF(A3<>"",A2&" "&B2,IF(A4<>"",A2&" "&B2&","&B3,IF(A5<>"",A2&" "&B2&","&B3&","&B4,IF(A6<>"",A2&" "&B2&","&B3&","&B4&","&B5,A2&" "&B2)))),"")

 

[AkaTrouble]

.

thank you

it was a challenge for me to get that far with what i have been learning. it was an exercise for me to test my growing skills.

your feedback and example is valued and has been read several times in order to absorb, i was aware of some of the pitfalls of my method and have encountered the "&" option, on this occasion that would of probably only added to the moments of confusion in getting a working formula.

your solution is so much less complicated than mine although has elements i need to analyze more

thanks again for feedback and improvements

 

[István Hirsch]

If the names are in col A and the strings to concatenate in col B, copy B2 to C1 (now „AC”)
and enter into:

B2:

=IF(A2<>"",A2&" "&B2,C1&","&B2)

C2:

=IF(COUNTIF(C2,"*,*")=0,INDIRECT("C"&ROW()+COUNTIF(C$2:C20,LEFT(C2,FIND(" ",C2))&"*")-1),"")

and copy them down as long as there are data in col B. Do not change this layout. If more than 20 strings belong to some names change number 20 in C2 to a higher value.

Excel Workbook
	A	B	C	D
1			AC
2	Harry	AC	Harry AC	Harry AC,DA,PC,KA
3		DA	Harry AC,DA
4		PC	Harry AC,DA,PC
5		KA	Harry AC,DA,PC,KA
6	Stu	DA	Stu DA	Stu DA,KA
7		KA	Stu DA,KA
8	Neena	PC	Neena PC	Neena PC
9	Pihu	KA	Pihu KA	Pihu KA,NA,RE,PK
10		NA	Pihu KA,NA
11		RE	Pihu KA,NA,RE
12		PK	Pihu KA,NA,RE,PK
13	Kenny	NE	Kenny NE	Kenny NE,RA,TE
14		RA	Kenny NE,RA
15		TE	Kenny NE,RA,TE
16	Bori	AC	Bori AC	Bori AC,DA,PC,KA
17		DA	Bori AC,DA
18		PC	Bori AC,DA,PC
19		KA	Bori AC,DA,PC,KA
20	Soma	DA	Soma DA	Soma DA,KA
21		KA	Soma DA,KA
Sheet

 

[shuklaankur281190]

Oh woww its work as you said it can be extended to include more,,,, Thanks Mr. Aka