Need to concatenate single column data

[Naga]

Hello,

I am struggling to get a solution where the structure is not static like below. I tried multi row formula, but it was giving a syntax error.

Conditions

1. Need the concatenated data to be in the 1st row of the sequence start. Here it is "A" in record 1, which is a key and separator from next block of data

2. It is 2 rows of data sample that needs to be brought in this format and then it has to be separated. Output 2 given below

Record #	Input	Output
1	A*15	A*15|B*16|C*17|D*19|E*ab|F*45|G*def|H*DTP|I*456
2	B*16
3	C*17
4	D*19
5	E*ab
6	F*45
7	G*def
8	H*DTP
9	I*456
10	A*LE	A*LE|B*RS|C*45|D*32|E*123|F*124|G*156|I*129|
11	B*RS
12	C*45
13	D*32
14	E*123
15	F*124
16	G*156
17	I*129

2nd output

Output	A	B	C	D	E	F	G	H	I
A*15|B*16|C*17|D*19|E*ab|F*45|G*def|H*DTP|I*456	A*15	B*16	C*17	D*19	E*ab	F*45	G*def	H*DTP	I*456
A*LE|B*RS|C*45|D*32|E*123|F*124|G*156|I*129|	A*LE	B*RS	C*45	D*32	E*123	F*124	G*156		I*129

Thanks

Naga

[VianneyM]

hi @Naga,

attached the workflow that allows you to concatenate the data.

You need to add the multirow formula to avoid having empty rows after using the cross tab

Let me know if you have any questions

Best,

Vianney

[Naga]

Hi @VianneyM ,

Thanks for the quick response. I got the answer for my 2nd output. however, need solution for this to get concatenated.

INput

Record #	Input
1	A*15
2	B*16
3	C*17
4	D*19
5	E*ab
6	F*45
7	G*def
8	H*DTP
9	I*456
10	A*LE
11	B*RS
12	C*45
13	D*32
14	E*123
15	F*124
16	G*156
17	I*129

Output

Record #	Input	Output
1	A*15	A*15|B*16|C*17|D*19|E*ab|F*45|G*def|H*DTP|I*456
2	B*16
3	C*17
4	D*19
5	E*ab
6	F*45
7	G*def
8	H*DTP
9	I*456
10	A*LE	A*LE|B*RS|C*45|D*32|E*123|F*124|G*156|I*129|
11	B*RS
12	C*45
13	D*32
14	E*123
15	F*124
16	G*156
17	I*129

Thanks
Naga

[VianneyM · Accepted solution]

Hi @Naga,

Attached the complete solution from your input 

INput

Record #	Input
1	A*15
2	B*16
3	C*17
4	D*19
5	E*ab
6	F*45
7	G*def
8	H*DTP
9	I*456
10	A*LE
11	B*RS
12	C*45
13	D*32
14	E*123
15	F*124
16	G*156
17	I*129

to this output

Let me know if you have questions

Best,

Vianney

[dho111]

Similar solution using Multi-Row Formula tool. Set "A" as the key. 

However, @VianneyM solution using Tile tool is the best way to avoid formula. 

[Naga]

Awesome...solved my problem....Thank you so much 

[Naga · Accepted solution]

Thanks @VianneyM ...solved my problem...I am learning lot from this group....:)