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,

- 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

- 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

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

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

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

eg: INDIRECT(“C2”)

A sample could be found @ https://docs.google.com/spreadsheets/d/1ZI7NTLF1aFqGtOGSDd9UIvwWnSD_hMUQW9tRN5PvJD0/edit#gid=1948860668

### Like this:

Like Loading...

*Related*

Tags: 2D data to 1D data, excel data table, excel translate table data to column or row data, read spreadsheet cell data table, spreadsheet table

This entry was posted on March 9, 2014 at 2:17 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

## Leave a Reply