OFFSET has the advantage of requiring only one cell to be used as a base reference ( col_data_top), so extending the source data range with further data does not need redefining the source data range in the formula, one has only to copy-paste into an extended target range. Note that copying also into I5 returns 0, not an error. The same result is obtained in matrix_data2_top_left (I1) with =OFFSET(col_data_top,(ROW()-ROW(matrix_data2_top_left))*span+(COLUMN()-COLUMN(matrix_data2_top_left)),0) Note that copying also into D5 gives an error, since the resulting formula refers to a cell outside col_data (A1:A16). Which is then copied into the rest of matrix_data. Then matrix_data_top_left (D1 here) contains =INDEX(col_data,(ROW()-ROW(matrix_data_top_left))*span+(COLUMN()-COLUMN(matrix_data_top_left)+1),1) The span (in C1) gives the number of columns. It shows several ranges with their defined names (in italics in the following).Īll defined names can be replaced by direct absolute references to the corresponding cells. The pros and cons of each one will be given after explicit examples, with reference to the figure. The essential functions to be used are INDEX or OFFSET. The idea is to give here something that can likely be used with minor adaptations to the questions listed above, which may also serve as a reference for future related questions. Matrix (with a span of 4 columns here) 1 2 3 4 Some of the answers appear to be "upgradeable" to something more encompassing. Move data from multiple columns into single row * Sorting three columns into six, sorted horizontally by surname using excel *ĭivide data in one column into more column in excel How to use VBA to reshape data in excel * How do transform a "matrix"-table to one line for each entry in excelĬonvert columns with multiple rows of data to rows with multiple columns in Excel. Writing a macro to transpose 3 columns into 1 row How to split one column into two columns base on conditions in EXCEL * Move content from 1 column to 3 columns * I keep updating this list, as new similar (or equal) questions are added: I have answered some of them, marked with *. There are a few similar or related questions. What about an even more complex case: reshape a matrix of width W to width N*W? Are there formulas to convert data in a column to a matrix or to a row?Īnd to convert from/to other combinations?
0 Comments
Leave a Reply. |