N/APosted on - 07/14/2011
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.
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.
In a new worksheet, type the following data:
Type the following formula in cell C1: =OFFSET($A$1,(ROW()-1)*5+INT((COLUMN()-3)),MOD(COLUMN()-4,1))
Fill this formula across to column G, and then down to row 4.
Adjust the column sizes as necessary. Note that the data is now displayed in cells C1 through G4
The formula can be interpreted as
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.