Converting Spreadsheet Data table to a Single Row/Column Data

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 @ https://docs.google.com/spreadsheets/d/1ZI7NTLF1aFqGtOGSDd9UIvwWnSD_hMUQW9tRN5PvJD0/edit#gid=1948860668

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: