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?
Solution
In B1 enter:
=INDEX($A:$A,COLUMNS($A:A)+5*(ROWS($1:1)-1),0)
and copy both across and downward:
- Find the first row of a 2d which contains all required associative elements
- Filter rows of a 2d array which contain two specified column values
- Find the first occurring array row which contains a search value and return the first level key
- Search for a column value in a 2d array and return its first level key
- Return the first level key upon finding a qualifying subarray in a multidimensional array
- Search for a value in the 3rd level of a 3d array and return the first level key
- (PHP) specific array sort
- Sort a multi-dim array by a column
- Sort a multi-dimensional array by a column
- Declare a fixed value to each of an array of key values to populate a new associative array
- How to declare a new associative element of a specific first level element?
- How to push a new row into a 2d array?
- Sort all rows of a 2d array to align with the sorted result of a specific row
- Sort a row of a 2d array and apply the same movements to all other rows
- Sort a 2d array by three columns
- Sort a 2d array by a column of d-n-Y formatted dates
- Sort a 2-D array by a column
- Sort an associative array by Y-m-d formatted keys
- Array sorted by PHP gets unsorted when accessed in a Javascript for() loop
- Sorting an associative array with `arsort()` prints 1 instead of the sorted array
- How to sort a 2-d array by a column?
- How to sort multi-dimensional array by a column and preserve the non-numeric first level keys
- Sort an associative array by key in a descending direction
- PHP copy reference from array to array
- Comparing two arrays ignoring element order in Ruby
- How to divide a matrix in MATLAB into N^2 segments each with NxN elements?
- Explode string only once on first occurring newline character
- How to make all array keys the same as their respective value?
- what is the wrong with this javascript function?
- Batch matrix multiplication in numpy