Import Excel data into Matrix, then search Matrix for specified text?

I am new to Matlab, but I do have some experience programming in C.

My goal: I want to set up a code that will search through my company's monthly production Excel spreadsheet and get the average numbers per employee.

When I import the data that I want to be analyzing, I get this:

 dataMatrix = 

    [235.2060]    'FELIPE'    [225.5390]    [ 63]    'PAUL'      [228.4580]
    [       0]    [   NaN]    [     NaN]    [NaN]    [   NaN]    [     NaN]
    [115.4260]    'FELIPE'    [112.9200]    [100]    'PAUL'      [114.5300]
    [       0]    [   NaN]    [     NaN]    [NaN]    [   NaN]    [     NaN]
    [ 72.8160]    'JOSH'      [ 71.1500]    [140]    'STEVEN'    [ 71.3500]
    [       0]    [   NaN]    [     NaN]    [NaN]    [   NaN]    [     NaN]
    [ 24.8040]    'JOSH'      [ 24.1000]    [342]    'STEVEN'    [ 24.6400]
    [       0]    [   NaN]    [     NaN]    [NaN]    [   NaN]    [     NaN]
    [ 30.4120]    'ANDREW'    [ 29.2900]    [190]    'EVAN'      [ 29.5700]
    [       0]    [   NaN]    [     NaN]    [NaN]    [   NaN]    [     NaN]
    [165.0640]    'ANDREW'    [159.8400]    [ 64]    'EVAN'      [162.4700]
    [396.2380]    'JUAN'      [428.8000]    [ 20]    'FRAN'      [401.5530]
    [179.1440]    'JUAN'      [184.2850]    [  7]    [   NaN]    [       0]

This is the information I want to set up my code to analyze. I was thinking I can loop through this matrix and search--just for example--for Felipe (first location would be Row1,Col2) and then take his actual, achieved number for the day (Row1,Col3 -> 225.5390) and compare it to his goal (Row1,Col1 -> 235.2060) to get a percent error. I would then hope to store this percent error and continue searching for the next spot where Felipe is (Row2,Col2) and do the same with Row2,Col3 (actual) compared with Row2,Col1 (goal) to again calculate the percent error to get a running average of how they are doing throughout the month.

Every time a specified employee is found, it finds the new average percent error and stores it. I realize that I might have to set up a new loop for each employee, or might have a really long if/else chain.

The above dataMatrix is for one day, and I would need to do the same for each day in the month.

Here is my code so far:

% Initialization
filename = 'January2016COPY';
nrows = 13;
ncols = 6;
dataMatrix = zeros(nrows,ncols);

% 'i' represents the worksheet currently working on.
i = 1;

% Begin 'for' loop which will read each worksheet and then increment to
%   the next worksheet, all the while reading values and storing them.
for i=1:2   %  <-- This would normally go from 1:31 to read the whole month
    % 'sheet' is the current sheet in the Excel workbook being looked at
    sheet = i; 
    % Set range to search through
    xlRange = 'H6:M18';
    % Read in data from specified 'filename' in the range specified above
    [num,txt,raw] = xlsread(filename,sheet,xlRange);
    % Store the raw data into matrix 
    dataMatrix = [raw]

      % Begin 'for' loop which will check each cell in 'dataMatrix' for names
      %   and calculate their walls.
      for c = 1:ncols
          for r = 1:nrows
              if (dataMatrix(r,c) = 'FELIPE')
                  FelipeCurrentDayWalls = ( dataMatrix(r,c+1) - ...
                      dataMatrix(r,c-1)./ dataMatrix(r,c-1) .* 100

end

I stopped midway through looping in the matrix because I kept getting unknown errors.

If I am going about this the wrong way, feel free to let me know. I don't care how sucky my code/approach is, I am simply here to learn :)

[dpb]

Let's start with the data file format...that surely isn't the organization inside the Excel spreadsheet is it? It looks like line/record-wrap has occurred???

As an aside, at least one of the issues is

if (dataMatrix(r,c) = 'FELIPE')

the equality operator in Matlab is '==', not '=' but it isn't the appropriate test for a string as it returns a logical array, not a single logical. strcmp or the like are more appropriate but let's get the data format straightened out before worrying about coding...

[Samuel Bland]

Here is what the spreadsheet looks like:

I have updated my code with your suggestions, here is my output:

The only thing I changed is my 2D for loop, which is here:

for c = 1:ncols
        for r = 1:nrows
            currentCell = dataMatrix(r,c)
            if strcmp('FELIPE',currentCell{r,c})
                FelipeCurrentDayWalls = ( dataMatrix(r,c+1) - ...
                    dataMatrix(r,c-1)./ dataMatrix(r,c-1) .* 100 )
            end %End 'if' statement
        end %End 'for' loop for nrows
end %End 'for' loop for ncols

[dpb]

"...what the spreadsheet looks like"

Still looks peculiar -- there are two groups of three columns that obviously go together but the first column seems an outlier or the similar data are missing for the second set of columns or it is applicable to both or...???

Whatever, to effectively solve the problem with Matlab you'll want to rearrange the data in a columnar format for each record for each sales rep somewhat like Kirby shows below. He assumed there that 1:3 and 4:6 are the two sets but that doesn't seem to correlate well with the actual sheet as shown above; you'll have to fix that up however it should be based on what the columns really mean...

After that, looks like he's given you a decent starting point.

Answer 1

[Kirby Fears]

Samuel,

You should probably take some time to read up on Matlab basics.

Below is a solution to get you started.

filename = 'January2016COPY.xlsx'; % xlsx or xls?
xlRange = 'H6:M18'; % this is static across sheets / books?
[~,sheetNames] = xlsfinfo(filename);

for i = 1:numel(sheetNames),
    sheet = sheetNames{i};
    [~,~,raw] = xlsread(filename,sheet,xlRange);
    % stack data
    data = [raw(:,1:3); raw(:,4:6)];
    % drop trash rows
    keepIdx = arrayfun(@(r)ischar(data{r,2}),1:size(data,1))';
    data = data(keepIdx,:);
    % split out your variables
    names = data(:,2);
    actuals = cell2mat(data(:,3));
    goals = cell2mat(data(:,1));
    % stack data into table
    record = (1:length(names))';
    if i==1,
        fullTable = table(record,names,actuals,goals);
    else
        record = max(fullTable.record) + record;
        dataTable = table(record,names,actuals,goals);
        fullTable = outerjoin(fullTable,dataTable,'MergeKeys',true);
    end
end

% compute errors
fullTable.errors = 100 - 100*fullTable.actuals./fullTable.goals;
summary(fullTable(strcmpi(fullTable.names,'felipe'),:));