CODEWITHSUNDEEP
arraysexceltranspose
Matti
Matti

Reputation: 391

How do I convert a single vertical row into multiple row array in Excel?

I am currently trying to convert a single vertical row in Excel into an array in multiple rows of 5. So, for example I have

A1 1000
A2 1001
A3 1002
A4 1003
A5 1004
A6 1005
A7 1006
...

And I'm trying to convert it to a multiple row array like below:

1000 1001 1002 1003 1004
1005 1006 ...

I had previously done this in Excel but I cannot remember how I did it. I used the TRANSPOSE function but I cannot remember the logic on how to shift the reference by 5 on the next row. Does someone know a function I could use?

Upvotes: 0

Views: 160

Answers (2)

Yasin YALÇIN
Yasin YALÇIN

Reputation: 9

Select your vertical row. Copy all row with CTRL+C. Click any empty cell. Right-click on mouse. Click s and click Enter.

Or click with mouse. Like this. https://i.sstatic.net/8HCii.jpg

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

In B1 enter:

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

and copy both across and downward:

enter image description here

Upvotes: 1

Related Questions