Find a value in excel file

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?

Answer 1

[Geoff Hayes]

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!

Answer 2

[Image Analyst]

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.

Answer 3

[Bereketab Gulai]

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...