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

Question

ABCDE1)2343456432)2353465432NULLNULLNULLNULLNULLABCDEFNULLNULLNULLNULL1)

5487432562)562342348653)8745523390NULLNULLNULLNULLNULL

Above table should be transformed into below one.

1)234345643ABCDE2)2353465432ABCDE1)548743256ABCDE  (F next cell)2)56234234865ABCDE  (F next cell)3)8745523390ABCDE  (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 Answer

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 Answer

Hi, @the_amvy

My solution like @gawa ,

Input           Col1Col2Col3Col4Col5       ABCDE       1)234345643       2)2353465432                   ABCDE       F           1)548743256       2)56234234865       3)8745523390                   ABCDE       FG          1)31415161       2)32425262       3)33435363                               Output           IDCol1Col2Col3Col4Match1Match2Match3Match4Match5Match6Match71)234345643ABCDE  2)2353465432ABCDE  1)548743256ABCDEF 2)56234234865ABCDEF 3)8745523390ABCDEF 1)31415161ABCDEFG2)32425262ABCDEFG3)33435363ABCDEFG