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

21 views (last 30 days)
Samuel Bland
Samuel Bland on 17 Feb 2016
Commented: dpb on 17 Feb 2016
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
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 on 17 Feb 2016
"...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.

Sign in to comment.

Answers (1)

Kirby Fears
Kirby Fears on 17 Feb 2016
Edited: Kirby Fears on 17 Feb 2016
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);
record = max(fullTable.record) + record;
dataTable = table(record,names,actuals,goals);
fullTable = outerjoin(fullTable,dataTable,'MergeKeys',true);
% compute errors
fullTable.errors = 100 - 100*fullTable.actuals./fullTable.goals;

Community Treasure Hunt

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

Start Hunting!