Data Transformation: Pasting 1st line values in next lines till Null line hit.

[the_amvy]

A	B	C	D	E
1)	23	43	456	43
2)	23	534	65	432
NULL	NULL	NULL	NULL	NULL
A	B	C	D	E
F	NULL	NULL	NULL	NULL
1)	54	87	432	56
2)	56	234	234	865
3)	87	455	233	90
NULL	NULL	NULL	NULL	NULL

Above table should be transformed into below one.

1)	23	43	456	43	A	B	C	D	E
2)	23	534	65	432	A	B	C	D	E
1)	54	87	432	56	A	B	C	D	E  (F next cell)
2)	56	234	234	865	A	B	C	D	E  (F next cell)
3)	87	455	233	90	A	B	C	D	E  (F next cell)

I was thinking Multi field formula tool can be useful here.

Can this type of transformation is possible or we have to do this manually. 

[gawa · Accepted solution]

hi @the_amvy 

For example, attached workflow works well to transpose data as you wish. I'm not sure this is robust way to handle your real data, but could help you somewhat, I guess.

[flying008 · Accepted solution]

Hi, @the_amvy 

My solution like @gawa , 

Input
Col1	Col2	Col3	Col4	Col5
A	B	C	D	E
1)	23	43	456	43
2)	23	534	65	432
A	B	C	D	E
F
1)	54	87	432	56
2)	56	234	234	865
3)	87	455	233	90
A	B	C	D	E
F	G
1)	31	41	51	61
2)	32	42	52	62
3)	33	43	53	63
Output
ID	Col1	Col2	Col3	Col4	Match1	Match2	Match3	Match4	Match5	Match6	Match7
1)	23	43	456	43	A	B	C	D	E
2)	23	534	65	432	A	B	C	D	E
1)	54	87	432	56	A	B	C	D	E	F
2)	56	234	234	865	A	B	C	D	E	F
3)	87	455	233	90	A	B	C	D	E	F
1)	31	41	51	61	A	B	C	D	E	F	G
2)	32	42	52	62	A	B	C	D	E	F	G
3)	33	43	53	63	A	B	C	D	E	F	G

[the_amvy]

Hi @gawa, thanks a lot for your solution. It helped a lot !!

[the_amvy]

Hi @flying008,  Thanks for providing the workflow. I transformed the data using only multi field and multi row formula tools, but this is very easy and short. Will use this next time.