How to convert following data to column in format given below-CodePudding

How to convert following data to column in excel (any formula or method)?

I have a column with randon numbers as below

i want to convert above to like this

0     10     25     45     95     102
0      0     25     45     95     102
0      0      0     45     95     102
0      0      0      0     95     102
0      0      0      0     0      102
0      0      0      0     0        0

how to convert above in excel with formula or any shortcut way?

CodePudding user response：

If you have Excel 365, try this as a spill formula:

=IF(TRANSPOSE(ROW(A1:A6))>ROW(A1:A6),TRANSPOSE(A1:A6),0)

My formula in cell B1 is:

=A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6

But if you have Excel365 probably you may benefit from TEXTJOIN

Formula in cell B2 is: =SUBSTITUTE(B1;A2;"0";1)

Drag down and you will get the output desired.