Join all different groups separated by vertical bar

[motilulla]

Hello,

I have 1 to 10 groups in columns C:L and he column N there is count of the groups

I need to join numbers are in row within columns C:L minimum 1 maximum 5 in the column P separated by vertical bar as shown in the example below

For a more detail the image is attached. Pease suggest formula or VBA

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4			Group	Group	Group	Group	Group	Group	Group	Group	Group	Group		Total Group		Join All Groups
5			1	2	3	4	5	6	7	8	9	10		Total Group		Join All Groups
6						4		6	7	8	9			5		4 | 6 | 7 | 8 | 9
7				2	3					8		10		4		2 | 3 | 8 | 10
8					3	4			7	8				4		3 | 4 | 7 | 8
9			1	2					7	8				4		1 | 2 | 7 | 8
10									7	8	9			3		7 | 8 | 9
11			1	2								10		3		1 | 2 | 10
12									7	8				2		7 | 8
13					3			6						2		3 | 6
14						4	5		7	8		10		5		4 | 5 | 7 | 8 | 10
15						4		6		8				3		4 | 6 | 8
16						4				8	9			3		4 | 8 | 9
17						4	5				9			3		4 | 5 | 9
18							5	6	7					3		5 | 6 | 7
19					3					8		10		3		3 | 8 | 10
20						4	5			8				3		4 | 5 | 8
21					3				7			10		3		3 | 7 | 10
22						4		6			9			3		4 | 6 | 9
23									7	8		10		3		7 | 8 | 10
24				2		4		6						3		2 | 4 | 6
25									7	8	9			3		7 | 8 | 9
26								6		8		10		3		6 | 8 | 10
27			1				5					10		3		1 | 5 | 10
28						4			7	8		10		4		4 | 7 | 8 | 10
29					3	4				8	9			4		3 | 4 | 8 | 9
30			1							8		10		3		1 | 8 | 10
31							5		7			10		3		5 | 7 | 10
32							5	6			9			3		5 | 6 | 9
33						4			7			10		3		4 | 7 | 10
34			1			4		6			9	10		5		1 | 4 | 6 | 9 | 10
35			1			4	5	6				10		5		1 | 4 | 5 | 6 | 10
36			1	2	3			6				10		5		1 | 2 | 3 | 6 | 10
37					3		5		7	8		10		5		3 | 5 | 7 | 8 | 10
38					3		5		7		9	10		5		3 | 5 | 7 | 9 | 10
39			1		3					8				3		1 | 3 | 8
40			1	2				6	7		9			5		1 | 2 | 6 | 7 | 9
41				2		4	5		7	8				5		2 | 4 | 5 | 7 | 8
42				2	3	4	5					10		5		2 | 3 | 4 | 5 | 10
43				2	3			6		8		10		5		2 | 3 | | 8 | 10
44			1				5	6			9			4		1 | 5 | 6 | 9
45				2		4				8		10		4		2 | 4 | 8 | 10
46			1	2	3		5							4		1 | 2 | 3 | 5
47			1			4					9	10		4		1 | 4 | 9 | 10
48			1		3	4					9	10		5		1 | 3 | 4 | 9 | 10
49							5	6	7			10		4		5 | 6 | 7 | 10
50			1	2		4	5							4		1 | 2 | 4 | 5
51				2			5		7	8				4		2 | 5 | 7 | 8
52						4	5				9	10		4		4 | 5 | 9 | 10
53				2	3		5	6	7					5		2 | 3 | 5 | 6 | 7
54				2						8		10		3		2 | 8 | 10
55			1											1		1
56						2								1		2
57
58

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[mackc557]

=TEXTJOIN("|",TRUE,$C6:$L6)

 

[sxhall]

Hi Moti,

Get Excel updated and you can use the below in column P...

=TEXTJOIN(" | ",1,C6:L6)

But for 2000(!) use this in column P...

=SUBSTITUTE(TRIM(CONCATENATE(IF(C6="","",C6 & " "),IF(D6="","",D6 & " "),IF(E6="","",E6 & " "),IF(F6="","",F6 & " "),IF(G6="","",G6 & " "),IF(H6="","",H6 & " "),IF(I6="","",I6 & " "),IF(J6="","",J6 & " "),IF(K6="","",K6 & " "),IF(L6="","",L6 & " ")))," "," | ")

Steven

 

[kweaver]

After 2016, TEXTJOIN would do it for you:

=TEXTJOIN(" | ",TRUE,D7:M7)][code]
You can probably find a UDF if you search for it. I don't happen to have one.

 

[motilulla]

=TEXTJOIN("|",TRUE,$C6:$L6)

mackc557, formula gave a #NAME? Error, thank you for your help... as @ kweaver said it work after 2016 version

Kind regards,
Moti

 

[motilulla]

Hi Moti,

Get Excel updated and you can use the below in column P...

=TEXTJOIN(" | ",1,C6:L6)

But for 2000(!) use this in column P...

=SUBSTITUTE(TRIM(CONCATENATE(IF(C6="","",C6 & " "),IF(D6="","",D6 & " "),IF(E6="","",E6 & " "),IF(F6="","",F6 & " "),IF(G6="","",G6 & " "),IF(H6="","",H6 & " "),IF(I6="","",I6 & " "),IF(J6="","",J6 & " "),IF(K6="","",K6 & " "),IF(L6="","",L6 & " ")))," "," | ")

Steven

sxhall, yes your formula worked magical

Thank you so much for your kind help

Kind regards,
Moti

 

[motilulla]

After 2016, TEXTJOIN would do it for you:

=TEXTJOIN(" | ",TRUE,D7:M7)][code]
You can probably find a UDF if you search for it. I don't happen to have one.

kweaver, yor are right TEXTJOIN is not computable with my version but it is solved by sxhall formula. Thank you for your attention

Kind regards,
Moti

 

[Fluff]

@motilulla whilst I know that you always state what version of Excel you are running, I would also suggest updating your account to show you version as there is now an option for "2003 or older"

 

[motilulla]

@motilulla whilst I know that you always state what version of Excel you are running, I would also suggest updating your account to show you version as there is now an option for "2003 or older"

Fluff, account update is done. As it gives me update Office Version 2003 or older I have checked this box.

And in the section about you: I have put the note; but in fact I am using Excel Version XL2000.

Thank you for the information

Kind Regards,
Moti