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

22 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
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
% Assuming your variable names are: "Var1" and "Var2"
% Read file as table with the set options
% Import value corresponding to only "E" (value along the same row as "E", but in different column)
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 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
mergedtable = join(mergedtable, currenttable, 'Keys', 'Var1');
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.




Community Treasure Hunt

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

Start Hunting!