Archive for March, 2014

Converting Spreadsheet Data table to a Single Row/Column Data

March 9, 2014

Say given you have a table of data in your spreadsheet and you want to convert it in to a column of data where each row of data in the table is converted in to a column of data itself. You can do this easily using the spread sheet functions in the cells by doing the following,

  1. assume the data to be indexed 1 through n (n=total number of data cells in the data table)

    eg: data table of 5×8 will have 40 indexes

  2. for each index number associated with the data-cell determine the row and column for that data (you can easily do this with MOD and DIVIDE/INT commands, but mind the special edge case)

    eg: (with above example) index 8 corresponds to row 2, column 3

  3. Using “ADDRESS” command you can determine the cell address of a <row>,<column>

    eg: “2,3” corresponds to cell address “C2”

  4. Using “INDIRECT” command you can retrieve the value of a cell address

    eg: INDIRECT(“C2”)

A sample could be found @