How to convert a set of data into rows in MS Excel

How to convert a set of data into rows in MS Excel

OK so the problem here is you want to convert every 16 columns into a single row.

As far as my knowledge about ms excel is you need to apply a formula to convert column to row.

Here is the screenshot of the excel sheet where I converted data from column to row.

Example

1. In a new worksheet, type the following data:
   A1: a
   A2: d
   A3: asd
   A4: dasd
   A5: asd
   A6: asd
   A7: asd
   A8: ads
   A9: d
   A10: asdas
   A11: as
   A12: ads
   A13: asd
   A14: a
   A15: aas
   A16: sda
   A17: a
   A18: a
   A19: dasd
   A20: dasd
2. Type the following formula in cell C1: =OFFSET($A$1,(ROW()-1)*5+INT((COLUMN()-3)),MOD(COLUMN()-4,1))
3. Fill this formula across to column G, and then down to row 4.
4. Adjust the column sizes as necessary. Note that the data is now displayed in cells C1 through G4

5. The formula can be interpreted as

   OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

   where:

   • f_row = row number of this offset formula
   • f_col = column number of this offset formula
   • rows_in_set = number of rows that make one record of data
   • col_in_set = number of columns of data

   I hope this will solve your query and if you need more help then contact me.

   Thanks.