Monday, June 8, 2015

Transpose data from Table to One Column / Multiple rows with heading


We know trick to transpose data from row to column & vice versa, however in the edge of ERP many system requires data in Table to be transposed in to one column which is very difficult to do manually when you have n numbers of rows of data.

Formula used is offset x no. of Rows & y no. of column from Starting cell.

here you can name your starting cell as START by going to name manager,

--Number of row to be offset is 2 divide by number of columns in table;
--Number of column to be offset is "z" divide by "z" divide where z is number of columns in table;

Refer attached sheet with formula to do so..