How to convert following data to column in excel (any formula or method)?
I have a column with randon numbers as below
0
10
25
45
0
25
45
0
45
i want to convert above to like this
0 10 25 45
0 0 25 45
0 0 0 45
how to convert above in excel with formula or any shortcut way?
If you have Excel 365, try this as a spill formula:
=IF(TRANSPOSE(ROW(A1:A6))>ROW(A1:A6),TRANSPOSE(A1:A6),0)
You could use Byrow and Textjoin if you wished to put them all in a single column:
=BYROW(IF(TRANSPOSE(ROW(A1:A6))>ROW(A1:A6),TRANSPOSE(A1:A6),0),LAMBDA(array,TEXTJOIN(" ",,array)))
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.