Creating Several Data Sheets
5 views (last 30 days)
I have three excel sheets each containg data with 300 rows and 120 columns. Each sheet have the first column as date (t1, t2, t3, ...) and other columns with data on variable X (X1,...,X119). The sheet 2 and 3 contains data on variable Y and Z, respectively. This is a diagonal data. The data on X1 is up to 160, the data on X2 is up to 161, the data on X3 is up to 162,..., the data on X119 is up to 300. Simarlarly for the other variables.
What I need to do is to get 119 different data sheets containg data on (t1, X1, Y1, Z1), (t2, X2, Y2, Z2),..., (t119, X119, Y119, Z119). Could anyone please help me with this?
Thank you in anticipation.
Bob Thompson on 25 Mar 2021
If you want to performa the reorganization with MATLAB here is an outline of one possible process to do so.
1) Read that three sheets into MATLAB. The current best method is readmatrix, as David mentioned, but a slightly older method is readtable, or xlsread (last resort). You will need one command for each sheet in excel.
2) Data organization in MATLAB. If the data was read directly in as a matrix, then it is quickest to read it in as a single array, so you don't actually need to organize your data again. If read in as tables then the data should be extracted into a 3D matrix.
3) Write data back out to Excel. This can be done with writematrix, or xlswrite. Be sure to call the specific sheet you would like to write to. You will need to perform this operation for each set of data, which will not be efficient. Looping won't speed things up, bit it will allow you to make your code cleaner.