2

I am currently struggling to find out how a long column vector in excel can be divided into several line vectors.

E.g.

% one long column vector
20,857
20,888
14,938
0,01
20,872
20,825
14,992
0,007
20,786
20,756
14,96
0,015
20,855
20,9
14,981
0,004
20,921
20,838
14,972
0,004

into

% several line vectors
[20,857 20,888 14,938 0,01;
20,872 20,825 14,992 0,007;
20,786 20,756 14,96 0,015;
% and so on]

Depicted in a picture from Excel

Of course I could go ahead and always copy x (e.g. x = 4) lines, right click on my destination and and choose "insert" and select "transpose" from the menu. However with an increasing length of the column vector this is a time extensive option.

Can someone come up with a better solution?

Journeyman Geek
  • 133,878
Timon
  • 67
  • 4

3 Answers3

1

With data in column A starting at A1, in B1 enter:

=INDEX($A:$A,COLUMNS($A:A)+4*(ROWS($1:1)-1),0)

and copy both downwards and to the right:

enter image description here

Gary's Student
  • 19,636
1

assuming your line vector started in cell A1, you could use index for this.

Where ever you want the grid to start use the below formula, then drag across 4 cells, and then down as many as desired:

  =INDEX($A:$A,(ROWS($D$4:D4)*4)-3+COLUMNS($D$4:D4)-1,1)

enter image description here

If your data started in A2, then replace =INDEX($A:$A with =INDEX($A2:$A5000 or amend to where it may start.

PeterH
  • 7,595
1

If data starts from A2, in B2 enter:

=OFFSET($A$1,MOD(COLUMN(A1)+3,4)+ROW(A1)*4-3,)

Drag the fill handle right and then down. enter image description here

Erin
  • 176