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

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?

Answer 1

[fred ssemwogerere]

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");

[Abdullah Azzam]

Thanks for the help but when using the provided code due to the fact that Cell E is a mearged cell, when running the code the value along the same row as "E" it gave empty cell that is part of the merged cell.

I have manage to find away around that. But I would like to hear your answer in case of mearged cells.

[fred ssemwogerere]

Hello,the question may not be so clear, but my understanding is that, "E" , is contained over a number of merged rows in your spreadsheet program, while its corresponding numerical value for extraction is contained in a single cell which is not exactly along the same row dimension as the character,"E". As a result you are getting, "NaN", for your output.

To work around this, adding this line of code after "readtable" could do nicely;

T=fillmissing(T,'next');

% As long as your corrresponding numerical value is within the width of a merged cell 
% in your column of characters, the character, "E", for example, should take on the value of
% the next non-missing value.

[Abdullah Azzam]

I don't know why. But the code worked for few runs and now whenever I am trying to use it it gives this error message:

Error using matlab.io.ImportOptions/getNumericSelection (line 456)
Unknown variable name: 'Var1'.

Error in matlab.io.ImportOptions/set.SelectedVariableNames (line 143)
                rhs = getNumericSelection(obj,rhs);

Error in Nebraska (line 32)
    optns.SelectedVariableNames={'Var1','Var2','Var3'};

even when I try to run the same sample that I did first and worked fine it gives that error when I try to run them again, what is a possible reason for that?

[Abdullah Azzam]

How about solving the error above. Why does it work sometimes, and someother time it tells me that error?

[fred ssemwogerere]

The variable names i used in the code i.e. "Var1" and "Var2" were arbitrary names. You should replace them with the name of column variables in your spreadsheet program you are trying to import. Every instance of "Var1" or "Var2" in the code above should be replaced with the column variable names in the sheets of your spreadheet program.

[fred ssemwogerere]

Another probable reasson that could be affecting your exection of the code could be that the sheets you are importing have differing column variable names.

[Abdullah Azzam]

How may I know what are my column variable name? Are they the same as column number? I tried to look that up but didn't find it.

[fred ssemwogerere]

Hello, by column variable name, i imply the pre-defined names for each column of data in your sheets. The best way to know what your column variable names are for a given sheet, say Sheet 1, is to preview the data you are importing.

% After this line of code, assuming your data is coming from "Sheet 1"
optns=detectImportOptions('myfile.xlsx','Sheet',1);
% You can preview the data with this line of code, to check for column headers (the bold labels above each column)
preview('myfile.xlsx',optns);
% This will help you know the column variable names

[Guillaume]

The simplest way to get code that works for your files is to attach an example file. That way we don't have to guess what the variable names are, what problems may happen because of the formatting, etc.

If the data is confidential you can replace it with dummy data.

[Abdullah Azzam]

Here is a sample of the attached files where 1 is working fine while 3 gives the mentioned error when I am trying to get the value correspond to either MILLINGS or SHOULDER MILLING.

[fred ssemwogerere]

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]

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.

Answer 2

[Guillaume]

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.