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

Asked By 210 points N/A Posted on -
qa-featured

I think there is a simple solution to this issue but I don't know VBA.

I have a 15-minute interval information for 395 consecutive days. The information is in a 6290 x 6 (r x c) matrix, so each 16 rows represents the information for day. I'd like to somehow convert this immense block of information in to a 395 row matrix where each row is a complete single day.

Any suggestions?

SHARE
Answered By 0 points N/A #82593

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

qa-featured

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.

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.

     

 

Login/Register to Answer

Related Questions