How to read data from one excel file and write it to another excel file

As an example I have data in 1st excel file as follows:

Stage x y z

1 20.6 31.6 12.3

1 41.5 51.4 71.1

2 30.1 81.2 92.3

2 16.4 11.5 62.7

3 20.8 31.9 12.0

So I want to read all data in x column that refer to 'stage' '1', add them and then take out its average. Then write that calculated average to 2nd excel file wherein we write data(of 3 variables x,y and z) referring to the 'stage' column of 2nd excel document which data before executing this program as follows :

Stage x y z

1

1

2

2

3 and after execution as follows :

Stage x y z

1 31.05 41.5 41.7

1 31.05 41.5 41.7

2 23.25 46.35 77.5

2 23.25 46.35 77.5

3 20.8 31.9 12.0

Can anyone help me on this?

Answer 1

[Megna Hari]

I'm not quite sure what you mean by x column of stage 1. Is stage 1: 20.6 31.6 12.3?

I would do blah=xlsread('filename.xls') and if your blah is set up like x=[1 20.6 31.6 12.3, 1 41.5 51.4 71.1, 2 30.1 81.2 92.3, 2 16.4 11.5 62.7, 3 20.8 31.9 12.0] like the way you wrote it then use [rows,cols,vals] = find(blah==1), ==2, ==3 etc. to find the columns where the stages start so you could reorganize them.

Unless your data is already set up in columns and not like the matrix I set up above?

[KRUNAL]

Here (on this forum)I am unable to create a table. As per image analyst's suggestion I have attached part of file data in the format in which it is existing.I hope that now my main question is clear.

Answer 2

[Image Analyst]

Please attach your workbook so we can test. I think it would be something like

t = readtable(excelFullFileName);
stage = t.Stage; % Extract the "Stage" column.
rowsToExtract = stage == 1;  % Find rows where stage = 1.
% Get output table.
tOutput = t(rowsToExtract);  % Copy only stage=1 rows to output variable.
% Write out
writetable(tOutput, fullOutputFileName, 'WriteRowNames', true);

But I can't test it until you supply your file.

[KRUNAL]

This is the file from where I need to read data

[KRUNAL]

and this is the file I need to write into

[KRUNAL]

yeah it is quite similar to the code what you have written above except for I was not able to write the command for reading data whose rows are unknown. Should I write similar command to write data for unknown rows because in file2 also I need to write data only where stage=1

[KRUNAL]

In the very beginning I had wrote this code. It had no errors but at the same time it also didn't give me any output

pfile = 'file location';
efile = 'file location';
sheet = 'sheet1' ;
xlsread(pfile,sheet,'B3:B12') ;
TVD = 0; %TVD%
Est = 0; %Easting%
Nrth = 0; %Northing%
n = 0;

switch (n)
  case 1
      data1= efile(efile(:,3)==1,4);
      data2= efile(efile(:,3)==1,5);
      data3= efile(efile(:,3)==1,6);
      nTVD = TVD + pfile(pfile(:,2)==1,4) ;
      fTVD = nTVD ;
      xlswrite(efile,sheet,data1) ;
      nEst = Est + pfile(pfile(:,2)==1,5) ;
      fEst = nEst;
      xlswrite(efile,sheet,data2) ;
      nNrth = Nrth + pfile(pfile(:,2)==1,6) ;
      fNrth = nNrth ;
      xlswrite(efile,sheet,data3);
  case 2
      data1= efile(efile(:,3)==1,4);
      data2= efile(efile(:,3)==1,5);
      data3= efile(efile(:,3)==1,6);
      nTVD = TVD + pfile(pfile(:,2)==2,4) ;
      fTVD = nTVD/2 ;
      xlswrite(efile,sheet,data1) ;
      nEst = Est + pfile(pfile(:,2)==2,5) ;
      fEst = nEst/2 ;
      xlswrite(efile,sheet,data2) ;
      nNrth = Nrth + pfile(pfile(:,2)==2,6) ;
      fNrth = nNrth/2;
      xlswrite(efile,sheet,data3);
end