Find a value in excel file

63 views (last 30 days)
Lisa
Lisa on 15 Aug 2014
Answered: Bereketab Gulai on 27 May 2020
Hi everybody! Hope that somebody can help me, I'm relative new to matlab and are wondering if I have an excel file with a table containing both names an numbers, and I want to use a number related to one of the names in a function when the name is an input argument, am I supposed to use a loop or what?
EX:
Name Bought Sold
Eric 2 13
Linda 7 4
Jasmine 3 8
Fredrik 10 9
I want to have the name as an argument ex:
Function CalculateTotal (name)
The function should then "find" the right name and use the values related to that person so that for exampel;
CalculateTotal (Eric)
Should be 2+13=15
Any one who can help me?

Accepted Answer

Geoff Hayes
Geoff Hayes on 15 Aug 2014
Edited: Geoff Hayes on 15 Aug 2014
Viktoria - you can use xlsread to read the data from the file, either dividing it up into numeric and text data, or all data into one cell array. Since you have a header column, the latter might be the way to go
[~,~,rawData] = xlsread('myFile.xls);
In your example, rawData should be a 5x3 cell array.
Now, use find to determine where in the first column of rawData is there a match to the name. If there is a match, find will return an index of the row in which that name is found. If there is no match, then an empty matrix is returned
name = 'Eric';
% look at all row elements in the first column returning
% the first match only
idx = find(strcmp(rawData(:,1),name),1);
if ~isempty(idx)
total = sum(cell2mat(rawData(idx,2:end)));
else
total = 0;
end
In the above, if our index is not empty, then we grab all elements in the row idx from the second column onwards (using rawData(idx,2:end)). This returns a cell array so we need to convert it to a matrix which we do with cell2mat. Then we can easily sum the elements giving us our total.
Note that if you wish to use a function, your signature should be something like
function [total] = CalculateTotal (name)
Rather than opening the Excel file each time you call this function, you may just want to pass the rawData as an input
function [total] = CalculateTotal (rawData,name)
Try the above and see what happens!

More Answers (2)

Image Analyst
Image Analyst on 15 Aug 2014
I think the simplest, cleanest, and most elegant way is to use a table. This works great but only if you have version R2013b or later:
function test2
% The main routine.
clc;
t = readtable('D:\Temporary stuff\Book1.xlsx')
% Call the function
[numberBought, numberSold] = CalculateTotal('Eric', t)
% The function definition.
function [bought, sold] = CalculateTotal(personName, t)
row = ismember(personName, t.Name) % Find row where this person is stored.
if row > 0
bought = t.Bought(row);
sold = t.Sold(row);
else
% Name was not found.
sold = 0;
bought = 0;
end
Of course you can make it more robust by calling lower() to make it case insensitive, using try catch, alerting user with warndlg() if the name is not found, handling the case where the same name shows up in multiple rows, etc.

Bereketab Gulai
Bereketab Gulai on 27 May 2020
Alternative with actxserver:
excelApp = actxserver("excel.Application");
excelApp.Visible = false;
book1 = excelApp.Workbooks.Open('D:\Temporary stuff\Book1.xlsx');
% Sheet item 1 ...
sheetOne = book1.Sheets.Item(1);
foundInterfaceObj = sheetOne.Range("A:A").Find('Eric');
The returned Interface provides lots functions you may need. Row will give the row number, Value for the cell...

Products

Community Treasure Hunt

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

Start Hunting!

Translated by