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

Asked By 210 points N/A Posted on -

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

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
A9: d
A10: asdas
A11: as
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.