Get the value of a cell that is in the same row of matching cell in excel to matlab

9 views (last 30 days)
Hi guys and thanks in advance.
I have plenty of excel sheet that I am combining togather. However, the locations of the information in some sheet differ from those of others and I am trying to generalize my code. Consider the shown example
Example.PNG
Lets say that I want to import the value correspond to "E" which is "5" as in the example. However, I can't simply ask for reading cell B7. because in some sheets the table would shift up or down, left or right. Is there a way similar to Vlookup used in excel that can be used to find the location of "E" and take the correspond value "5" and import it to matlab?

Accepted Answer

fred  ssemwogerere
fred ssemwogerere on 6 Feb 2020
Hello, this can do nicely;
% Am assuming your file,"myfile.xlsx" (arbitrary name), is an excel file, and in the current (working) folder of Matlab
optns=detectImportOptions('myfile.xlsx');
% Assuming your variable names are: "Var1" and "Var2"
optns.SelectedVariableNames={'Var1','Var2'};
optns=setvartype(optns,{'Var1','Var2'},{'categorical','double'});
% Read file as table with the set options
T=readtable('myfile.xlsx',optns);
% Import value corresponding to only "E" (value along the same row as "E", but in different column)
valImport=T.Var2(T.Var1=="E");
  12 Comments
fred  ssemwogerere
fred ssemwogerere on 8 Feb 2020
Hello, both files seem to have a similar format, so to extract your required information, this can do nicely for both files;
optns=detectImportOptions('3.xls','Sheet',1);
% Override the default "Data Range" set by "optns", to that covering the start of your data.
optns.DataRange='A16:P26'; % Range of your data based on both files supplied ("1.xls" and"3.xls")
% Remember Matlab will read each column from the spreadsheet, merged or unmerged, with different column headers such as; "Var1","Var2",......
% To extract data from the first column and the column of percentages (%), set their respective variable names as "Var1" and "Var3"
optns.SelectedVariableNames={'Var1','Var3'};
% Set read data-type of the columns
optns=setvartype(optns,{'Var1','Var3'},{'categorical','double'});
% Set the handling of missing or empty rows by filling with a scalar "0"
optns.MissingRule = 'fill';
optns = setvaropts(optns,{'Var1','Var3'},'FillValue',0);
% Read file as table with the set options
T=readtable('3.xls',optns);
% Import value corresponding to only "MILLINGS"
valImport=T.Var3(T.Var1=="MILLINGS");
Abdullah Azzam
Abdullah Azzam on 8 Feb 2020
Thanks for the help. I have used a turn around method by making the matlab takes all the data and save them in new excel files to then extract them again from that file. It took longer runing time but made the job. I will try that method out for future cases and thanks for your effort and time.

Sign in to comment.

More Answers (1)

Guillaume
Guillaume on 6 Feb 2020
Edited: Guillaume on 6 Feb 2020
readtable should be capable of finding the data range on its own, so use that to import your data.
Then for merging, simply use join possibly specifying the join variable. join will automatically match the row containing E in one table with the row containing E in the other, regardless of where it is. So the code would go something like this:
mergedtable = [];
for sheettomerge = ?:?
currenttable = readtable(??);
if isempty(mergedtable)
mergedtable = currenttable; %first file, no merge yet
else
mergedtable = join(mergedtable, currenttable, 'Keys', 'Var1');
end
end
Depending on the type of merge you're doing you may want to use innerjoin or outerjoin instead of join. For more fleshed out code, we need more details about the actual sheets.

Products


Release

R2017a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!