Export variables from excel based on a defined column category and time

I would like to select and export a subset of values from an excel document based on both a category and time.

An example of the data is attached. Column A represents a time, column B represents a text event, and columns E-L represent individual datapoints for analysis.

What I would like to do, is when an event is defined (for example, 'Baseline' [B8]), to collect the values every 1 minute for columns E, G, I and K, until the next event is defined (for example 'Succinate' [B40]), and continue to collect every 1 minute until the next event ('Reperfusion' [B615]) until the end of the experiment, then export these values into a separate csv file.

At the moment I am doing this manually and it is extremely time consuming, especially as I have over 300 files to analyse, so I am going slightly insane. All files are formatted in a similar way, but are of different durations in time depending on the experiment. Does anyone know if this is possible using MATLAB? Any advice immensely appreciated!

Annabel

[Annabel Sorby-Adams]

Please also note the time increment (31 cells = 1 minute) is consistent for all experiments.

Answer 1

[Voss]

Something like this would work, if the exact times you're looking for (i.e., every 1 minute starting at an event, until the next event/end of file) are really always there, which they are in this particular example file.

input_file_name = 'Example_Experiment_2023-08-02.xlsx';
output_file_name = 'data_subset.csv';

T = readtable(input_file_name,'VariableNamingRule','preserve');

cols = [1 2 5 7 9 11]; % I'm including columns A and B in the output file too - feel free to modify
all_times = T{:,1};

event_rows = find(~cellfun(@isempty,T{:,2}));
N_events = numel(event_rows);
event_rows(end+1) = size(T,1);

data = cell(1,N_events);
for ii = 1:N_events
    times = all_times(event_rows(ii)):all_times(event_rows(ii+1));
    [~,idx] = ismember(times,all_times);
    data{ii} = T(idx,cols);
end

T_new = vertcat(data{:});
writetable(T_new,output_file_name);

% check
disp(readtable(output_file_name,'VariableNamingRule','preserve'))

    Time [min]      Event Name       1A: O2 concentration [µM]    1B: O2 concentration [µM]    1A: Amp raw [V]    1B: Amp raw [V]
    __________    _______________    _________________________    _________________________    _______________    _______________

        0.2       {'Baseline'   }              171.37                      171.83                  0.3361              0.319     
        1.2       {0×0 char     }              168.17                      168.59                  0.3532             0.3354     
       1.27       {'Succinate'  }              166.93                      168.38                  0.3555              0.336     
       2.27       {0×0 char     }              153.07                      154.47                  0.5407             0.5002     
       3.27       {0×0 char     }              132.27                      133.79                  0.7163             0.6932     
       4.27       {0×0 char     }              110.25                      112.02                  0.8575             0.8485     
       5.27       {0×0 char     }              87.513                      89.532                  0.9673             0.9683     
       6.27       {0×0 char     }              64.453                      66.747                  1.0489             1.0566     
       7.27       {0×0 char     }              41.259                      43.919                  1.1042             1.1182     
       8.27       {0×0 char     }              18.473                      21.508                  1.1362             1.1542     
       9.27       {0×0 char     }             -0.8695                      0.9792                  1.1045             1.1582     
      10.27       {0×0 char     }             -0.9583                      0.2044                  0.8311             0.8718     
      11.27       {0×0 char     }             -1.0381                      0.1703                   0.631              0.667     
      12.27       {0×0 char     }             -0.9671                      0.1447                  0.4831              0.517     
      13.27       {0×0 char     }             -0.9938                      0.1362                  0.3772              0.409     
      14.27       {0×0 char     }              -0.976                      0.1192                  0.3033             0.3294     
      15.27       {0×0 char     }             -1.0026                      0.1022                  0.2516             0.2721     
      16.27       {0×0 char     }             -1.0204                      0.1022                   0.215             0.2298     
      17.27       {0×0 char     }              -1.047                      0.0937                  0.1882             0.1993     
      18.27       {0×0 char     }             -1.0115                      0.0851                  0.1688             0.1771     
      19.27       {0×0 char     }             -1.0293                      0.0766                  0.1541             0.1591     
      20.27       {0×0 char     }             -1.0293                      0.0766                  0.1438             0.1444     
      20.43       {'Reperfusion'}             -0.3283                      0.1533                  0.5722             0.3142     
      21.43       {0×0 char     }              90.947                       90.63                  1.2374             1.3028     
      22.43       {0×0 char     }              128.91                      128.49                  1.3692             1.4404     
      23.43       {0×0 char     }              143.55                      142.94                  1.5093              1.591     
      24.43       {0×0 char     }              148.88                      148.13                  1.6413             1.7382     
      25.43       {0×0 char     }              150.55                      149.74                  1.7618             1.8812     
      26.43       {0×0 char     }              150.91                      150.06                   1.885             2.0125     
      27.43       {0×0 char     }              150.78                      149.95                  1.9944             2.1324     
      28.43       {0×0 char     }              150.49                      149.76                  2.0996             2.2512     
      29.43       {0×0 char     }              150.15                      149.53                   2.207             2.3573     
      30.43       {0×0 char     }              149.86                      149.36                  2.3019             2.4576     
      31.43       {0×0 char     }               149.6                      149.15                  2.3962             2.5662     
      32.43       {0×0 char     }              149.32                      149.03                   2.488              2.662     
      33.43       {0×0 char     }              149.11                       148.9                  2.5808             2.7527     
      34.43       {0×0 char     }              148.98                      148.82                  2.6622             2.8459     
      35.43       {0×0 char     }              148.79                      148.69                  2.7424             2.9272     
      36.43       {0×0 char     }              148.74                      148.69                  2.8208              3.014     
      37.43       {0×0 char     }              148.61                      148.65                  2.8992             3.0925     
      38.43       {0×0 char     }              148.49                      148.65                  2.9762             3.1725     
      39.43       {0×0 char     }              148.45                      148.64                  3.0413              3.251     
      40.43       {0×0 char     }              148.45                      148.62                  3.1064             3.3253     
      41.43       {0×0 char     }              148.52                      148.66                  3.1723             3.3985     

[Annabel Sorby-Adams]

This works perfectly! If there is only 40 seconds between one event/the next/the end of the experiment it doesn't matter if this number isn't included. I tried on another few files and it appears to skip over, so works perfectly.

One other question - is it possible to have this loop through multiple .xlsx files in folder and compile into a master .csv file? Or would this not be possible because the experiments have different variables/times? If so, even a compiled .csv file with a different tab for each experiment? Thanks so much again!

[Voss]

" is it possible to have this loop through multiple .xlsx files in folder and compile into a master .csv file?"

Should each file be processed independently of the others, or do you need the program to keep "counting" from the last event of one file, minute-by-minute, until the first event of the next file?

[Annabel Sorby-Adams]

Each file should be proccessed independently if possible!

[Voss]

In that case, you can use dir to get the names of the files in the folder, and loop over them, running the code in my answer each time through the loop. Store the new tables (T_new in the answer) in a cell array, and vertcat them all together after the loop and write the file. The code below does that.

You mentioned that the different files might have different variables in the headers, so I've included code here to rename the variables so they're standardized so the final table can be constructed. I assume that each file has the same number of columns and you want columns [1 2 5 7 9 11] from each.

your_folder = 'C:\whatever';
output_file_name = 'C:\whatever\master.csv';

S = dir(fullfile(your_folder,'*.xlsx'));

ffn = fullfile({S.folder},{S.name});

cols = [1 2 5 7 9 11]; % I'm assuming all files have the same column order

var_names = cellstr(('A':'L').'); % standard variable names: A-L

T_new = cell(1,numel(ffn)); % T_new is a cell array now

for jj = 1:numel(ffn)
    
    T = readtable(ffn{jj});
    
    T = renamevars(T,T.Properties.VariableNames,var_names); % rename T's variables
    
    all_times = T{:,1};
    
    event_rows = find(~cellfun(@isempty,T{:,2}));
    N_events = numel(event_rows);
    event_rows(end+1) = size(T,1);
    
    data = cell(1,N_events);
    for ii = 1:N_events
        times = all_times(event_rows(ii)):all_times(event_rows(ii+1));
        [~,idx] = ismember(times,all_times);
        data{ii} = T(idx,cols);
    end
    
    T_new{jj} = vertcat(data{:});
    
end

T_new = vertcat(T_new{:});

writetable(T_new,output_file_name);

[Annabel Sorby-Adams]

Thank you so much. Unfortunately I am getting the following error. I have attached the 4 files I am trying to batch process. I think it might be to do with 'event_rows'. Please let me know if you have any insight!

Error using  () 
Index in position 1 is invalid. Array indices must be positive integers or logical values.

Error in OROBOROS_NEW (line 24)
        data{ii} = T(idx,cols); 

[Voss]

The problem was some of the times were not exactly one minute apart, e.g., the code is looking for 31.23 but it was actually 31.230000000000001, etc., so I replaced the ismember call with ismembertol, and now it should work.

your_folder = '.';
output_file_name = './master.csv';

S = dir(fullfile(your_folder,'*.xlsx'));

ffn = fullfile({S.folder},{S.name});

cols = [1 2 5 7 9 11]; % I'm assuming all files have the same column order

var_names = cellstr(('A':'L').'); % standard variable names: A-L

T_new = cell(1,numel(ffn)); % T_new is a cell array now

for jj = 1:numel(ffn)
    
    T = readtable(ffn{jj},'VariableNamingRule','preserve');
    
    T = renamevars(T,T.Properties.VariableNames,var_names); % rename T's variables
    
    all_times = T{:,1};
    
    event_rows = find(~cellfun(@isempty,T{:,2}));
    N_events = numel(event_rows);
    event_rows(end+1) = size(T,1);
    
    data = cell(1,N_events);
    for ii = 1:N_events
        times = all_times(event_rows(ii)):all_times(event_rows(ii+1));
        [~,idx] = ismembertol(times,all_times,1e-4);
        data{ii} = T(idx,cols);
    end
    
    T_new{jj} = vertcat(data{:});
    
    [~,fn,ext] = fileparts(ffn{jj});
    T_new{jj}.file = repmat({[fn ext]},size(T_new{jj},1),1);
    T_new{jj} = T_new{jj}(:,[end 1:end-1]);
    
end

T_new = vertcat(T_new{:});

writetable(T_new,output_file_name);

% check
disp(readtable(output_file_name))

                  file                    A          B            E          G         I         K   
    ________________________________    _____    __________    _______    _______    ______    ______

    {'Experiment_239_Analysis.xlsx'}     1.27    {'s'     }     166.93     168.38    0.3555     0.336
    {'Experiment_239_Analysis.xlsx'}     2.27    {0×0 char}     153.07     154.47    0.5407    0.5002
    {'Experiment_239_Analysis.xlsx'}     3.27    {0×0 char}     132.27     133.79    0.7163    0.6932
    {'Experiment_239_Analysis.xlsx'}     4.27    {0×0 char}     110.25     112.02    0.8575    0.8485
    {'Experiment_239_Analysis.xlsx'}     5.27    {0×0 char}     87.513     89.532    0.9673    0.9683
    {'Experiment_239_Analysis.xlsx'}     6.27    {0×0 char}     64.453     66.747    1.0489    1.0566
    {'Experiment_239_Analysis.xlsx'}     7.27    {0×0 char}     41.259     43.919    1.1042    1.1182
    {'Experiment_239_Analysis.xlsx'}     8.27    {0×0 char}     18.473     21.508    1.1362    1.1542
    {'Experiment_239_Analysis.xlsx'}     9.27    {0×0 char}    -0.8695     0.9792    1.1045    1.1582
    {'Experiment_239_Analysis.xlsx'}    10.27    {0×0 char}    -0.9583     0.2044    0.8311    0.8718
    {'Experiment_239_Analysis.xlsx'}    11.27    {0×0 char}    -1.0381     0.1703     0.631     0.667
    {'Experiment_239_Analysis.xlsx'}    12.27    {0×0 char}    -0.9671     0.1447    0.4831     0.517
    {'Experiment_239_Analysis.xlsx'}    13.27    {0×0 char}    -0.9938     0.1362    0.3772     0.409
    {'Experiment_239_Analysis.xlsx'}    14.27    {0×0 char}     -0.976     0.1192    0.3033    0.3294
    {'Experiment_239_Analysis.xlsx'}    15.27    {0×0 char}    -1.0026     0.1022    0.2516    0.2721
    {'Experiment_239_Analysis.xlsx'}    16.27    {0×0 char}    -1.0204     0.1022     0.215    0.2298
    {'Experiment_239_Analysis.xlsx'}    17.27    {0×0 char}     -1.047     0.0937    0.1882    0.1993
    {'Experiment_239_Analysis.xlsx'}    18.27    {0×0 char}    -1.0115     0.0851    0.1688    0.1771
    {'Experiment_239_Analysis.xlsx'}    19.27    {0×0 char}    -1.0293     0.0766    0.1541    0.1591
    {'Experiment_239_Analysis.xlsx'}    20.27    {0×0 char}    -1.0293     0.0766    0.1438    0.1444
    {'Experiment_239_Analysis.xlsx'}    20.43    {'open'  }    -0.3283     0.1533    0.5722    0.3142
    {'Experiment_239_Analysis.xlsx'}    21.43    {0×0 char}     90.947      90.63    1.2374    1.3028
    {'Experiment_239_Analysis.xlsx'}    22.43    {0×0 char}     128.91     128.49    1.3692    1.4404
    {'Experiment_239_Analysis.xlsx'}    23.43    {0×0 char}     143.55     142.94    1.5093     1.591
    {'Experiment_239_Analysis.xlsx'}    24.43    {0×0 char}     148.88     148.13    1.6413    1.7382
    {'Experiment_239_Analysis.xlsx'}    25.43    {0×0 char}     150.55     149.74    1.7618    1.8812
    {'Experiment_239_Analysis.xlsx'}    26.43    {0×0 char}     150.91     150.06     1.885    2.0125
    {'Experiment_239_Analysis.xlsx'}    27.43    {0×0 char}     150.78     149.95    1.9944    2.1324
    {'Experiment_239_Analysis.xlsx'}    28.43    {0×0 char}     150.49     149.76    2.0996    2.2512
    {'Experiment_239_Analysis.xlsx'}    29.43    {0×0 char}     150.15     149.53     2.207    2.3573
    {'Experiment_239_Analysis.xlsx'}    30.43    {0×0 char}     149.86     149.36    2.3019    2.4576
    {'Experiment_239_Analysis.xlsx'}    31.43    {0×0 char}      149.6     149.15    2.3962    2.5662
    {'Experiment_239_Analysis.xlsx'}    32.43    {0×0 char}     149.32     149.03     2.488     2.662
    {'Experiment_239_Analysis.xlsx'}    33.43    {0×0 char}     149.11      148.9    2.5808    2.7527
    {'Experiment_239_Analysis.xlsx'}    34.43    {0×0 char}     148.98     148.82    2.6622    2.8459
    {'Experiment_239_Analysis.xlsx'}    35.43    {0×0 char}     148.79     148.69    2.7424    2.9272
    {'Experiment_239_Analysis.xlsx'}    36.43    {0×0 char}     148.74     148.69    2.8208     3.014
    {'Experiment_239_Analysis.xlsx'}    37.43    {0×0 char}     148.61     148.65    2.8992    3.0925
    {'Experiment_239_Analysis.xlsx'}    38.43    {0×0 char}     148.49     148.65    2.9762    3.1725
    {'Experiment_239_Analysis.xlsx'}    39.43    {0×0 char}     148.45     148.64    3.0413     3.251
    {'Experiment_239_Analysis.xlsx'}    40.43    {0×0 char}     148.45     148.62    3.1064    3.3253
    {'Experiment_239_Analysis.xlsx'}    41.43    {0×0 char}     148.52     148.66    3.1723    3.3985
    {'Experiment_240_Analysis.xlsx'}      1.2    {'s'     }     169.21     169.53    0.3163    0.2896
    {'Experiment_240_Analysis.xlsx'}      2.2    {0×0 char}     153.49     157.91    0.4707    0.3937
    {'Experiment_240_Analysis.xlsx'}      3.2    {0×0 char}     132.57     137.69    0.6316    0.5598
    {'Experiment_240_Analysis.xlsx'}      4.2    {0×0 char}      110.4     116.12    0.7625     0.699
    {'Experiment_240_Analysis.xlsx'}      5.2    {0×0 char}     87.469     93.815    0.8633    0.8085
    {'Experiment_240_Analysis.xlsx'}      6.2    {0×0 char}     64.106     71.166    0.9359    0.8892
    {'Experiment_240_Analysis.xlsx'}      7.2    {0×0 char}     40.727     48.508    0.9856    0.9463
    {'Experiment_240_Analysis.xlsx'}      8.2    {0×0 char}     17.764      26.14     1.015    0.9828
    {'Experiment_240_Analysis.xlsx'}      9.2    {0×0 char}    -0.8962       4.93    0.9755    0.9981
    {'Experiment_240_Analysis.xlsx'}     10.2    {0×0 char}     -0.976     0.0937    0.7355    0.7902
    {'Experiment_240_Analysis.xlsx'}     11.2    {0×0 char}    -1.0026     0.0596    0.5595    0.5988
    {'Experiment_240_Analysis.xlsx'}     12.2    {0×0 char}    -1.0293     0.0426    0.4325    0.4622
    {'Experiment_240_Analysis.xlsx'}     13.2    {0×0 char}    -1.0026      0.017    0.3412    0.3646
    {'Experiment_240_Analysis.xlsx'}     14.2    {0×0 char}    -0.9938     0.0085    0.2754    0.2948
    {'Experiment_240_Analysis.xlsx'}     15.2    {0×0 char}    -1.0115          0    0.2305     0.246
    {'Experiment_240_Analysis.xlsx'}     16.2    {0×0 char}    -0.9494          0    0.1977    0.2103
    {'Experiment_240_Analysis.xlsx'}     17.2    {0×0 char}    -0.9583    -0.0085    0.1758    0.1841
    {'Experiment_240_Analysis.xlsx'}     18.2    {0×0 char}     -0.976     -0.017    0.1594    0.1635
    {'Experiment_240_Analysis.xlsx'}     19.2    {0×0 char}     -0.976    -0.0255     0.146    0.1481
    {'Experiment_240_Analysis.xlsx'}    19.93    {'open'  }     6.3264     1.9499    1.0283    0.7667
    {'Experiment_240_Analysis.xlsx'}    20.93    {0×0 char}      91.71      85.59    1.1152    1.1197
    {'Experiment_240_Analysis.xlsx'}    21.93    {0×0 char}     127.53     119.96    1.2296    1.1985
    {'Experiment_240_Analysis.xlsx'}    22.93    {0×0 char}     142.14        134    1.3451    1.2712
    {'Experiment_240_Analysis.xlsx'}    23.93    {0×0 char}     148.12     139.55    1.4733    1.3424
    {'Experiment_240_Analysis.xlsx'}    24.93    {0×0 char}     150.42     141.62    1.5871     1.404
    {'Experiment_240_Analysis.xlsx'}    25.93    {0×0 char}     151.18      142.6    1.6991    1.4576
    {'Experiment_240_Analysis.xlsx'}    26.93    {0×0 char}     151.21     143.14     1.798    1.5113
    {'Experiment_240_Analysis.xlsx'}    27.93    {0×0 char}     151.08     143.51     1.897    1.5619
    {'Experiment_240_Analysis.xlsx'}    28.93    {0×0 char}     150.98     143.86    1.9898    1.6037
    {'Experiment_240_Analysis.xlsx'}    29.93    {0×0 char}     150.64     144.14    2.0716    1.6452
    {'Experiment_240_Analysis.xlsx'}    30.93    {0×0 char}      150.5     144.47    2.1566    1.6944
    {'Experiment_240_Analysis.xlsx'}    31.93    {0×0 char}     150.26     144.85    2.2381    1.7409
    {'Experiment_240_Analysis.xlsx'}    32.93    {0×0 char}     150.12     145.28      2.31    1.7896
    {'Experiment_240_Analysis.xlsx'}    33.93    {0×0 char}     150.06     145.71    2.3866     1.838
    {'Experiment_240_Analysis.xlsx'}    34.93    {0×0 char}     149.96     146.18    2.4617    1.8832
    {'Experiment_240_Analysis.xlsx'}    35.93    {0×0 char}     149.94     146.68    2.5298    1.9284
    {'Experiment_240_Analysis.xlsx'}    36.93    {0×0 char}     150.04     147.24    2.5977    1.9776
    {'Experiment_240_Analysis.xlsx'}    37.93    {0×0 char}     149.95     147.89    2.6605    2.0217
    {'Experiment_240_Analysis.xlsx'}    38.93    {0×0 char}     149.99     148.57    2.7294    2.0692
    {'Experiment_240_Analysis.xlsx'}    39.93    {0×0 char}     150.17     149.34    2.7934    2.1177
    {'Experiment_240_Analysis.xlsx'}    40.93    {0×0 char}     150.16     149.95    2.8522    2.1624
    {'Experiment_240_Analysis.xlsx'}    41.93    {0×0 char}     150.22     150.73    2.9109     2.207
    {'Experiment_240_Analysis.xlsx'}    42.93    {0×0 char}     150.29     151.59    2.9731    2.2571
    {'Experiment_241_Analysis.xlsx'}     1.23    {'s'     }     177.01     174.54    0.3546    0.3698
    {'Experiment_241_Analysis.xlsx'}     2.23    {0×0 char}     164.28     165.99     0.514    0.5007
    {'Experiment_241_Analysis.xlsx'}     3.23    {0×0 char}     145.28     150.07    0.6792    0.6681
    {'Experiment_241_Analysis.xlsx'}     4.23    {0×0 char}     124.03     132.22     0.819    0.8137
    {'Experiment_241_Analysis.xlsx'}     5.23    {0×0 char}      101.5     113.21     0.931    0.9343
    {'Experiment_241_Analysis.xlsx'}     6.23    {0×0 char}     78.356     93.474    1.0167    1.0316
    {'Experiment_241_Analysis.xlsx'}     7.23    {0×0 char}     54.719     73.388    1.0783    1.1074
    {'Experiment_241_Analysis.xlsx'}     8.23    {0×0 char}     31.233     53.217    1.1186    1.1637
    {'Experiment_241_Analysis.xlsx'}     9.23    {0×0 char}     8.5446     33.216     1.139    1.2044
    {'Experiment_241_Analysis.xlsx'}    10.23    {0×0 char}    -0.7986     13.734    0.9667    1.2282
    {'Experiment_241_Analysis.xlsx'}    11.23    {0×0 char}    -0.8518     0.0596    0.7328    1.1531
    {'Experiment_241_Analysis.xlsx'}    12.23    {0×0 char}    -0.8695     -0.017    0.5585    0.9084
    {'Experiment_241_Analysis.xlsx'}    13.23    {0×0 char}    -0.8695    -0.0341    0.4308    0.7216
    {'Experiment_241_Analysis.xlsx'}    14.23    {0×0 char}    -0.8695    -0.0511    0.3382    0.5775
    {'Experiment_241_Analysis.xlsx'}    15.23    {0×0 char}    -0.8607    -0.0596    0.2734    0.4672
    {'Experiment_241_Analysis.xlsx'}    16.23    {0×0 char}    -0.8784    -0.0766    0.2275    0.3831
    {'Experiment_241_Analysis.xlsx'}    17.23    {0×0 char}    -0.8784    -0.0766    0.1945    0.3201
    {'Experiment_241_Analysis.xlsx'}    18.23    {0×0 char}     -0.905    -0.0851    0.1705    0.2727
    {'Experiment_241_Analysis.xlsx'}    19.23    {0×0 char}    -0.9405    -0.0937    0.1534    0.2362
    {'Experiment_241_Analysis.xlsx'}    20.23    {0×0 char}    -0.9228    -0.1022    0.1398    0.2086
    {'Experiment_241_Analysis.xlsx'}    21.23    {'open'  }    -0.9139    -0.1107    0.1288    0.1865
    {'Experiment_241_Analysis.xlsx'}    21.23    {'open'  }    -0.9139    -0.1107    0.1288    0.1865
    {'Experiment_241_Analysis.xlsx'}    22.23    {0×0 char}     73.432     82.499    1.2175     1.348
    {'Experiment_241_Analysis.xlsx'}    23.23    {0×0 char}     113.41     127.85    1.2899    1.4652
    {'Experiment_241_Analysis.xlsx'}    24.23    {0×0 char}     130.25     145.09    1.3505    1.5914
    {'Experiment_241_Analysis.xlsx'}    25.23    {0×0 char}     137.33     151.51    1.4114     1.714
    {'Experiment_241_Analysis.xlsx'}    26.23    {0×0 char}     140.32     153.75    1.4624    1.8267
    {'Experiment_241_Analysis.xlsx'}    27.23    {0×0 char}     141.61     154.48    1.5102    1.9337
    {'Experiment_241_Analysis.xlsx'}    28.23    {0×0 char}     142.19     154.63    1.5553    2.0353
    {'Experiment_241_Analysis.xlsx'}    29.23    {0×0 char}      142.6     154.61    1.6009    2.1297
    {'Experiment_241_Analysis.xlsx'}    30.23    {0×0 char}     142.84     154.52    1.6481    2.2227
    {'Experiment_241_Analysis.xlsx'}    31.23    {0×0 char}     143.16     154.46    1.6856    2.3134
    {'Experiment_241_Analysis.xlsx'}    32.23    {0×0 char}     143.55     154.35    1.7343     2.398
    {'Experiment_241_Analysis.xlsx'}    33.23    {0×0 char}     143.84     154.29     1.775    2.4821
    {'Experiment_241_Analysis.xlsx'}    34.23    {0×0 char}     144.35     154.21    1.8174    2.5649
    {'Experiment_241_Analysis.xlsx'}    35.23    {0×0 char}     144.84     154.18    1.8608    2.6418
    {'Experiment_241_Analysis.xlsx'}    36.23    {0×0 char}     145.27     154.15    1.9057    2.7202
    {'Experiment_241_Analysis.xlsx'}    37.23    {0×0 char}     145.94     154.15    1.9436    2.7976
    {'Experiment_241_Analysis.xlsx'}    38.23    {0×0 char}     146.57     154.12    1.9815    2.8646
    {'Experiment_241_Analysis.xlsx'}    39.23    {0×0 char}     147.28     154.12    2.0235    2.9397
    {'Experiment_241_Analysis.xlsx'}    40.23    {0×0 char}     148.06     154.15    2.0663      3.01
    {'Experiment_241_Analysis.xlsx'}    41.23    {0×0 char}     148.82     154.17    2.1024    3.0722
    {'Experiment_242_Analysis.xlsx'}      1.2    {'s'     }     177.06     173.84    0.4365     0.446
    {'Experiment_242_Analysis.xlsx'}      2.2    {0×0 char}      162.4     161.33    0.5875    0.5983
    {'Experiment_242_Analysis.xlsx'}      3.2    {0×0 char}     142.52     142.19     0.739    0.7672
    {'Experiment_242_Analysis.xlsx'}      4.2    {0×0 char}     120.55     120.94     0.866    0.9105
    {'Experiment_242_Analysis.xlsx'}      5.2    {0×0 char}     97.371     98.421    0.9666    1.0257
    {'Experiment_242_Analysis.xlsx'}      6.2    {0×0 char}     73.538      75.21    1.0425    1.1131
    {'Experiment_242_Analysis.xlsx'}      7.2    {0×0 char}     49.457     51.752     1.096    1.1756
    {'Experiment_242_Analysis.xlsx'}      8.2    {0×0 char}     25.527     28.422    1.1307    1.2156
    {'Experiment_242_Analysis.xlsx'}      9.2    {0×0 char}     2.8304     6.0625    1.1437    1.2345
    {'Experiment_242_Analysis.xlsx'}     10.2    {0×0 char}    -0.7808    -0.0511    0.9009    1.0009
    {'Experiment_242_Analysis.xlsx'}     11.2    {0×0 char}    -0.7719    -0.0851    0.6868     0.755
    {'Experiment_242_Analysis.xlsx'}     12.2    {0×0 char}    -0.7719    -0.1107    0.5261    0.5704
    {'Experiment_242_Analysis.xlsx'}     13.2    {0×0 char}    -0.8252    -0.1277    0.4078    0.4347
    {'Experiment_242_Analysis.xlsx'}     14.2    {0×0 char}    -0.8074    -0.1362    0.3235    0.3376
    {'Experiment_242_Analysis.xlsx'}     15.2    {0×0 char}    -0.8074    -0.1447    0.2628    0.2677
    {'Experiment_242_Analysis.xlsx'}     16.2    {0×0 char}    -0.8163    -0.1533     0.221    0.2178
    {'Experiment_242_Analysis.xlsx'}     17.2    {0×0 char}    -0.7631    -0.1618    0.1909    0.1827
    {'Experiment_242_Analysis.xlsx'}     18.2    {0×0 char}    -0.7986    -0.1703     0.168    0.1573
    {'Experiment_242_Analysis.xlsx'}     19.2    {0×0 char}    -0.7897    -0.1703    0.1523    0.1383
    {'Experiment_242_Analysis.xlsx'}     20.2    {0×0 char}    -0.8252    -0.1788      0.14    0.1238
    {'Experiment_242_Analysis.xlsx'}     21.2    {0×0 char}    -0.7986    -0.1788    0.1303    0.1133
    {'Experiment_242_Analysis.xlsx'}     22.2    {0×0 char}    -0.8341    -0.1873    0.1231    0.1043
    {'Experiment_242_Analysis.xlsx'}     23.2    {0×0 char}    -0.8518    -0.1958    0.1156     0.096
    {'Experiment_242_Analysis.xlsx'}     23.4    {'open'  }     -0.701    -0.1873    0.3526    0.0937
    {'Experiment_242_Analysis.xlsx'}     24.4    {0×0 char}     87.859      81.06    1.2148    1.3374
    {'Experiment_242_Analysis.xlsx'}     25.4    {0×0 char}      126.1     124.94    1.3179    1.4555
    {'Experiment_242_Analysis.xlsx'}     26.4    {0×0 char}     141.66     141.97    1.4222    1.5866
    {'Experiment_242_Analysis.xlsx'}     27.4    {0×0 char}     147.87      148.4    1.5285    1.7114
    {'Experiment_242_Analysis.xlsx'}     28.4    {0×0 char}     150.27     150.68     1.629     1.833
    {'Experiment_242_Analysis.xlsx'}     29.4    {0×0 char}     151.04     151.26    1.7224    1.9491
    {'Experiment_242_Analysis.xlsx'}     30.4    {0×0 char}     151.14     151.29     1.811    2.0513
    {'Experiment_242_Analysis.xlsx'}     31.4    {0×0 char}     151.13     151.17    1.8941    2.1596
    {'Experiment_242_Analysis.xlsx'}     32.4    {0×0 char}     151.05     151.02     1.975    2.2555
    {'Experiment_242_Analysis.xlsx'}     33.4    {0×0 char}     150.93      150.9    2.0551    2.3453
    {'Experiment_242_Analysis.xlsx'}     34.4    {0×0 char}      150.8     150.79    2.1258    2.4344
    {'Experiment_242_Analysis.xlsx'}     35.4    {0×0 char}     150.74     150.68    2.2027    2.5152
    {'Experiment_242_Analysis.xlsx'}     36.4    {0×0 char}     150.65     150.52    2.2734    2.5974
    {'Experiment_242_Analysis.xlsx'}     37.4    {0×0 char}     150.65     150.49    2.3422    2.6791
    {'Experiment_242_Analysis.xlsx'}     38.4    {0×0 char}     150.58     150.46     2.403    2.7502
    {'Experiment_242_Analysis.xlsx'}     39.4    {0×0 char}     150.61     150.48    2.4628    2.8279
    {'Experiment_242_Analysis.xlsx'}     40.4    {0×0 char}     150.63     150.48    2.5301     2.895
    {'Experiment_242_Analysis.xlsx'}     41.4    {0×0 char}     150.67     150.51    2.5842    2.9596
    {'Experiment_242_Analysis.xlsx'}     42.4    {0×0 char}     150.65     150.51    2.6447    3.0303
    {'Experiment_242_Analysis.xlsx'}     43.4    {0×0 char}     150.67     150.52    2.7021    3.0986
    {'Experiment_242_Analysis.xlsx'}     44.4    {0×0 char}     150.76     150.57    2.7567     3.154
    {'Experiment_242_Analysis.xlsx'}     45.4    {0×0 char}     150.94     150.68    2.8071    3.2169
    {'Experiment_242_Analysis.xlsx'}     46.4    {0×0 char}     151.03     150.71     2.862    3.2828

[Annabel Sorby-Adams]

This works perfectly! The only other thing if AT ALL possible would be if its is feasible to add a column/row at the start/between each experiment that includes the name of the file so I can discriminate them?

[Voss]

OK. I edited the code in my most recent comment to to include a column in each table containing the file name.

[Annabel Sorby-Adams]

This is sensational, thank you - the values and output file look perfect.

I was also wondering if you could please advise where in the code I would amend to the change the frequency of timing for exported values? The other step in my analysis pipeline is interpolating data (which I do seperately) using a calibration file. In the calibratoin file (attached), after every event ('h') I need to average the first 5 values, then move to the next event and repeat. I hope this makes sense/is a simple edit to the existing code, I just wasnt sure exactly where to make the changes.

Thank you again, SO MUCH!

[Voss]

This line:

times = all_times(event_rows(ii)):all_times(event_rows(ii+1));

generates a vector with spacing 1 from the current event time (all_times(event_rows(ii))) to at or near (but not exceeding) the next event time (all_times(event_rows(ii+1))).

To change the spacing from 1 to something else, you can do:

spacing = 3; % every 3 minutes instead of every 1 minute

times = all_times(event_rows(ii)):spacing:all_times(event_rows(ii+1));

But you better be sure that the times generated exist in the file, at least within the tolerance used in the next line:

[~,idx] = ismembertol(times,all_times,1e-4);

I'm not sure what changing the spacing has to do with averaging the first 5 samples after each 'h' event in the calibration file. Maybe you should ask a new question about that, becuase it seems quite different than the original question here.

[Annabel Sorby-Adams]

Thank you so much this is great to know how to change the time spacing. I'll submit a seperate question re averaging.

Thanks again!