Comparing elemental composition with databank

I have a string array 22x1 (Probe_RL), which contains elemental compositions. Next to it I have a database 13x2 (Datenbank). The composition of both should be compared. If there is a fit, the group should be added to the line in Probe_RL. The problem is, that the factors should have a tolerance of 0.2. So that for example 1Fe 0.9Si can fit to group 206 in the database. I tried the following and hope somebody can help me:

% Sample data
Probe_RL = {'1Fe', '1Fe', '1Fe,1Si', '1Fe', '0.35Si,1Fe', '0.19Fe,0.57K,1Si', '0.47Fe,0.68K,1Si', '1Si', '0.81Fe,1Si', '1Fe', '1Si', '1Fe', '0.44Fe,1Si', '1Na', '1Fe', '1Si', '0.43Fe,1Si', '0.6Si,1Fe', '1Fe', '0.15Fe,1Si', '1Fe', '1Fe'};
Datenbank = {'1Na,1U Group 204', '1Bi,1V Group 205', '1Fe,1Si Group 206', '0.11Si,1Mg Group 207', '0.83Mg,1Si Group 208', '0.1Ca,0.2Al,1Si Group 209', '0.83Ca,1Si Group 210', '0.67Ca,1Al,1Si Group 211', '0.5Co,1Na Group 212', '0.5Pb,1Co Group 213', '0.5Na,1Si Group 214', '1Al,1Ca Group 215', '0.5Co,1Ni Group 216'};

Datenbank = fliplr(Datenbank);
Probe_RL = fliplr(Probe_RL);


% Probenzusammensetzung in zwei Spalten teilen
[Probe_RL_2D, ~] = cellfun(@strsplit, Probe_RL, repmat({","}, length(Probe_RL), 1), 'UniformOutput', false);

% Probenummern hinzufügen
for i = 1:length(Probe_RL)
    Probe_RL_2D{i,2} = num2str(i);
end

% Proben mit der Datenbank vergleichen
for i = 1:length(Probe_RL)

    if ~isempty(Datenbank)

        % Proben mit der Datenbank vergleichen
        if numel(Probe_RL_2D{i,1:2}) > 1
            Probe_RL_2D{i,1:2} = Probe_RL_2D{i,1:2}(1);
        end

        % Konvertiere den Zeichenkettenvektor in einen Zellvektor
        Probe_RL_2D{i,1:2} = cellstr(char(''));

        Probe_RL_2D{i,1:2} = strjoin(Probe_RL_2D{i,1:2}, "");

        % Datenbank und Probe_RL vertikal anordnen
        Datenbank = fliplr(Datenbank);
        Probe_RL_2D = fliplr(Probe_RL_2D);

        j = find(abs(str2double(Probe_RL_2D{i,1:2}) - str2double(Datenbank(:,1:2))) < 0.2);

        if ~isempty(j)
            % Übereinstimmende Mineralien überprüfen
            if length(j) > 1
                Probe_RL(i,2) = "Unbekannt";
            else
                Probe_RL(i,2) = Datenbank(j(1),2);
            end
        else
            Probe_RL(i,2) = "Unbekannt";
        end
    else
        Probe_RL(i,2) = "Unbekannt";
    end
end

% Ergebnisse ausgeben
disp(Probe_RL)

[Jon]

Sorry, I'm not clear from your description what it is you want to do.

What do you mean when you say " If there is a fit, the group should be added to the line in Probe_RL"? Are you trying to label the elements in Probe_RL with a group number from the data base?

For the example data you give what would be the desired output?

[Tatjana Mü]

I am sorry. For example in line 3 and 4, group 206 should be set into the second column. So yes correct they should be labeled and if no mineral from the database is compatibel, then there should be written not identified (sorry it is written here in german "unbekannt". Like you can see, in front of the elements is a factor and these should have a tolerance of 0.1.

[Jon]

Wouldn't the 4th entry in Probe_RL be "unbekannt" as there is no pure Fe in the data base and the 4th entry in Probe_RL has Fe as it's only component?

Answer 1

[Jon]

Here is one approach, in which I put the data into tables and then use ismembertol to to do the comparision with a tolerance. I denote the group number for the samples with a numeric value of the group number (rather than a string) and if no match is found I have the group number as NaN rather than "unbekannt". You could modify this if it is important. To me it seemed better to maintain the group number, which is fundamentally a number not a string as a numerical value.

% Sample data
Probe_RL = {'1Fe', '1Fe', '1Fe,1Si', '1Fe', '0.35Si,1Fe', '0.19Fe,0.57K,1Si', '0.47Fe,0.68K,1Si', '1Si', '0.81Fe,1Si', '1Fe', '1Si', '1Fe', '0.44Fe,1Si', '1Na', '1Fe', '1Si', '0.43Fe,1Si', '0.6Si,1Fe', '1Fe', '0.15Fe,1Si', '1Fe', '1Fe'};
Datenbank = {'1Na,1U Group 204', '1Bi,1V Group 205', '1Fe,1Si Group 206', '0.11Si,1Mg Group 207', '0.83Mg,1Si Group 208', '0.1Ca,0.2Al,1Si Group 209', '0.83Ca,1Si Group 210', '0.67Ca,1Al,1Si Group 211', '0.5Co,1Na Group 212', '0.5Pb,1Co Group 213', '0.5Na,1Si Group 214', '1Al,1Ca Group 215', '0.5Co,1Ni Group 216'};

% Parameters
tol = 0.2; % tolerance for matching elements;

% Parse data and put into cell arrays
ProbeDat = cell(numel(Probe_RL),2);
for k = 1:numel(Probe_RL)
    % Break up individual entries using comma delimiters
    components = strsplit(Probe_RL{k},',');
    % Loop through components, getting element and quantity
    numComponents = numel(components);
    quantity = zeros(numComponents,1); % preallocate
    element = cell(numComponents,1); % preallocate
    for m = 1:numel(components)
        [quantity(m),element{m}] = numstr(components{m});
    end
    % Put into cell array
    ProbeDat{k,1} = element;
    ProbeDat{k,2} = quantity;
    ProbeDat{k,3} = Probe_RL{k};
end

RefDat = cell(numel(Datenbank),3);
for k = 1:numel(Datenbank)
    % Break up individual entries using comma and whitespace delimiters
    parts = strsplit(Datenbank{k},{',',' '});
    % Loop through components, getting element and quantity
    numParts = numel(parts);
    quantity = zeros(numParts-2,1); % preallocate
    element = cell(numParts-2,1); % preallocate
    for m = 1:numParts -2
        [quantity(m),element{m}] = numstr(parts{m});
    end
    group = str2double(parts{end});
    % Put into cell array
    RefDat{k,1} = element;
    RefDat{k,2} = quantity;
    RefDat{k,3} = group;
end

% Get alphabetically sorted list of all of the possible elements
allElements = sort(unique(vertcat(ProbeDat{:,1},RefDat{:,1})));
numVbls = numel(allElements); % total number of elements

% Make tables for probe data and databank (refernce) 
Probe = array2table(zeros(size(ProbeDat,1),numVbls),...
    'VariableNames',allElements);
Probe.Name = cell(height(Probe),1);
Probe = Probe(:,["Name";allElements]); % Make Name the first column
DataBank = array2table(zeros(size(RefDat,1),numVbls+1),...
    'VariableNames',['Group';allElements]);

% Loop through cell array assigning table row values to Probe table
% table has a column for each possible element, and entries with the
% composition. The leading column has the string name for the
% composition
for k = 1:height(Probe)
    for m = 1:numel(ProbeDat{k,1})
        Probe.(ProbeDat{k,1}{m})(k) = ProbeDat{k,2}(m);
    end
    Probe.Name{k} = ProbeDat{k,3};
    
end

% Loop through cell array assigning table row values to DataBank table
% table has a column for each possible element, and the entries with the
% composition, the leading column gives the group number for each
% composition
for k = 1:height(DataBank)
    for m = 1:numel(RefDat{k,1})
        DataBank.(RefDat{k,1}{m})(k) = RefDat{k,2}(m);
    end
    DataBank.Group(k) = RefDat{k,3};
end
disp(DataBank)

    Group    Al     Bi     Ca     Co     Fe    K     Mg     Na     Ni    Pb      Si     U    V
    _____    ___    __    ____    ___    __    _    ____    ___    __    ___    ____    _    _

     204       0    0        0      0    0     0       0      1    0       0       0    1    0
     205       0    1        0      0    0     0       0      0    0       0       0    0    1
     206       0    0        0      0    1     0       0      0    0       0       1    0    0
     207       0    0        0      0    0     0       1      0    0       0    0.11    0    0
     208       0    0        0      0    0     0    0.83      0    0       0       1    0    0
     209     0.2    0      0.1      0    0     0       0      0    0       0       1    0    0
     210       0    0     0.83      0    0     0       0      0    0       0       1    0    0
     211       1    0     0.67      0    0     0       0      0    0       0       1    0    0
     212       0    0        0    0.5    0     0       0      1    0       0       0    0    0
     213       0    0        0      1    0     0       0      0    0     0.5       0    0    0
     214       0    0        0      0    0     0       0    0.5    0       0       1    0    0
     215       1    0        1      0    0     0       0      0    0       0       0    0    0
     216       0    0        0    0.5    0     0       0      0    1       0       0    0    0

% Loop through rows in Probe table looking for a matching group in DataRref
% and lableling accordingly
numCol = width(Probe); % number of columns before adding label
for k = 1:height(Probe)
    idl = ismembertol(DataBank{:,2:end},Probe{k,2:numCol},tol,...
        'DataScale',1,'ByRows',true);
    if any(idl)
        % Label the group, assume just one match or none
        Probe.Group(k) = DataBank.Group(idl);
    else
        Probe.Group(k) = NaN; % NaN for not found
    end
end
% Reorder the columns so that the group match is the second column, just to
% make it be more readable
Probe = Probe(:,['Name';'Group';allElements]);
disp(Probe)

            Name            Group    Al    Bi    Ca    Co     Fe      K      Mg    Na    Ni    Pb     Si     U    V
    ____________________    _____    __    __    __    __    ____    ____    __    __    __    __    ____    _    _

    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'1Fe,1Si'         }     206     0     0     0     0        1       0    0     0     0     0        1    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'0.35Si,1Fe'      }     NaN     0     0     0     0        1       0    0     0     0     0     0.35    0    0
    {'0.19Fe,0.57K,1Si'}     NaN     0     0     0     0     0.19    0.57    0     0     0     0        1    0    0
    {'0.47Fe,0.68K,1Si'}     NaN     0     0     0     0     0.47    0.68    0     0     0     0        1    0    0
    {'1Si'             }     209     0     0     0     0        0       0    0     0     0     0        1    0    0
    {'0.81Fe,1Si'      }     206     0     0     0     0     0.81       0    0     0     0     0        1    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'1Si'             }     209     0     0     0     0        0       0    0     0     0     0        1    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'0.44Fe,1Si'      }     NaN     0     0     0     0     0.44       0    0     0     0     0        1    0    0
    {'1Na'             }     NaN     0     0     0     0        0       0    0     1     0     0        0    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'1Si'             }     209     0     0     0     0        0       0    0     0     0     0        1    0    0
    {'0.43Fe,1Si'      }     NaN     0     0     0     0     0.43       0    0     0     0     0        1    0    0
    {'0.6Si,1Fe'       }     NaN     0     0     0     0        1       0    0     0     0     0      0.6    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'0.15Fe,1Si'      }     209     0     0     0     0     0.15       0    0     0     0     0        1    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0
    {'1Fe'             }     NaN     0     0     0     0        1       0    0     0     0     0        0    0    0

% Make summary table with just names and group numbers, NaN means no match
% found, i.e. "unbekannt"
summary = Probe(:,{'Name','Group'})

summary = 22×2 table
            Name            Group
    ____________________    _____

    {'1Fe'             }     NaN 
    {'1Fe'             }     NaN 
    {'1Fe,1Si'         }     206 
    {'1Fe'             }     NaN 
    {'0.35Si,1Fe'      }     NaN 
    {'0.19Fe,0.57K,1Si'}     NaN 
    {'0.47Fe,0.68K,1Si'}     NaN 
    {'1Si'             }     209 
    {'0.81Fe,1Si'      }     206 
    {'1Fe'             }     NaN 
    {'1Si'             }     209 
    {'1Fe'             }     NaN 
    {'0.44Fe,1Si'      }     NaN 
    {'1Na'             }     NaN 
    {'1Fe'             }     NaN 
    {'1Si'             }     209 

% 

function [num,str] = numstr(s)
% splits string s into numeric and character parts
isNum = isstrprop(s,"digit")| isstrprop(s,"punct"); % digit or decimal point
num = str2double(s(isNum));
str = s(~isNum);
end

[Tatjana Mü]

@Jon Thank you so much for your help. I sadly get following error, do you know by chance how to solve this:

Error using table.init (line 390)

The VariableNames property is a cell array of character vectors. To assign multiple variable names, specify non-blank names in a

string array or a cell array of character vectors.

Error in array2table (line 64)

t = table.init(vars,nrows,rownames,nvars,varnames);

Error in Test_Mineralcomposition (line 105)

Probe = array2table(zeros(size(ProbeDat,1),numVbls),...

my ProbeDat1 looks like this:

'Fe'	1	'1Fe'
'Fe'	1	'1Fe'
2x1 cell	[1;1]	'1Fe,1Si'
2x1 cell	[0.910000000000000;1]	'0.91Si,1Fe'
2x1 cell	[0.350000000000000;1]	'0.35Si,1Fe'
3x1 cell	[0.190000000000000;0.570000000000000;1]	'0.19Fe,0.57K,1Si'
3x1 cell	[0.470000000000000;0.680000000000000;1]	'0.47Fe,0.68K,1Si'
'Si'	1	'1Si'
2x1 cell	[0.810000000000000;1]	'0.81Fe,1Si'
'Fe'	1	'1Fe'
'Si'	1	'1Si'
'Fe'	1	'1Fe'
2x1 cell	[0.440000000000000;1]	'0.44Fe,1Si'
'Na'	1	'1Na'
'Fe'	1	'1Fe'
'Si'	1	'1Si'
2x1 cell	[0.430000000000000;1]	'0.43Fe,1Si'
2x1 cell	[0.600000000000000;1]	'0.6Si,1Fe'
'Fe'	1	'1Fe'
2x1 cell	[0.150000000000000;1]	'0.15Fe,1Si'
'Fe'	1	'1Fe'
'Fe'	1	'1Fe'

[Jon]

So just to clarify,can you run the simple, self contained example code on your computer without errors?

I think you should be able to as it runs on the MATLAB answers site without errors.

In that case their is a problem with your adaptation/implementation of this idea for your actual problem. If so, please attach your .m files and any data files needed to reproduce the problem and I can try to help you further (probably not until Monday).

[Jon]

Please attach your code and I can help you with your error. It looks like you have a problem on line 105 of your code, but the error message that you copied and pasted doesn't list all of line 105 that might provide some further hints as to what went wrong. From the error that I see, most likely there is a problem with how you supplied the names of the variables in your call to array2table.

[Tatjana Mü]

@Jon , I thank you really much for your help. I attached my matlab code plus one "fake" sample and part of the databank.

[Jon]

When I run this code I don't get any errors. I have attached again here, with the file retrieval prompts commented out and hardoded to use 'Fake_Probe_Zusammensetzung.xlsx' just to demonstrate. I don't think it would matter unless you are running a very old version, but what version of MATLAB are you running?

clear all
close all
clc
%Blabla
warning off
clear;


%-----------------------Datenbank einlesen---------------------------------

RLImport = importdata('Datenbank_Matlab_Gruppierung2.xlsx');
Datenbank = RLImport.Tabelle1;

Datenbank=string(Datenbank);
[anzahl_lines,anzahl_columns]=size(Datenbank);



%-------------Dateien von SP_Zusammensetzung hochladen---------------------

% % % directory_name=uigetdir('','Ordner mit Messungen auswählen');
% % % [nur_file_name,pfad]=uigetfile({'*.xlsx','xlsx (*.xslx)';'*.*','all Files'},...
% % % 'PROBEN EINLESEN (probe_001.xlsx=',[directory_name '/'], 'Multiselect', 'on');
nur_file_name = 'Fake_Probe_Zusammensetzung.xlsx' % Hard code just for debugging

nur_file_name = 'Fake_Probe_Zusammensetzung.xlsx'

pfad = ''; % just for debugging
nur_file_name=cellstr(nur_file_name);
nur_file_name=sort(nur_file_name);
filename=strcat(pfad,nur_file_name);
anzahl_files=size(filename,2);


for xy=1:anzahl_files
    fid_in=fopen(char(filename(xy)),'r');
    
    filename_s = matlab.lang.makeValidName(nur_file_name);
    filename_s=string(filename_s);
    filename_s = erase(filename_s,"_csv");
    filename_s = erase(filename_s,"LiqQuant_");
    filename_c=cellstr(filename_s);
    for g=1:anzahl_files
        filename_f{g}=extractBefore(filename_c{g},11);
    end
    filename_s=string(filename_f);
    filename_s=strip(filename_s,'right',"_");
    
    
%------------------------Dateien einlesen----------------------------------
    
    tmpImport = importdata(filename{xy},',');
    SP_RL = tmpImport.Tabelle1;
    [anzahl_zeile,anzahl_elemente]=size(SP_RL);
    SP_RL=string(SP_RL);
    
%---------------------------Vergleich------------------------------------- 

SP_RL=cellstr(SP_RL);
for i=1:anzahl_files
Probe_RL = strcat(SP_RL);
Probe_RL=cellfun(@(s)join(unique(s),','),num2cell(Probe_RL,2));
Probe_RL = strip(Probe_RL,'left',",");
Probe_RL = strip(Probe_RL,'right',",");
end
Probe_RL=string(Probe_RL);

Probe_RL=cellstr(Probe_RL);
Probe_RL = rot90(Probe_RL);


Datenbank=cellstr(Datenbank);


% Initialisieren Sie den neuen Cell Array
Datenbank2 = cell(size(Datenbank, 1), 1);

% Schleife durch die Zeilen des ursprünglichen Cell Arrays und kombinieren Sie die Teile
for i = 1:size(Datenbank, 1)
    composition = Datenbank{i, 1};
    mineralName = Datenbank{i, 2};
    Datenbank2{i} = [composition ' ' mineralName];
end

Datenbank2 = rot90(Datenbank2);


end
% %--------------------Vergleich SP mit Datenbank----------------------------

% Parameters
tol = 0.2; % tolerance for matching elements;
% Parse data and put into cell arrays
ProbeDat = cell(numel(Probe_RL),2);
for k = 1:numel(Probe_RL)
    % Break up individual entries using comma delimiters
    components = strsplit(Probe_RL{k},',');
    % Loop through components, getting element and quantity
    numComponents = numel(components);
    quantity = zeros(numComponents,1); % preallocate
    element = cell(numComponents,1); % preallocate
    for m = 1:numel(components)
        [quantity(m),element{m}] = numstr(components{m});
    end
    % Put into cell array
    ProbeDat{k,1} = element;
    ProbeDat{k,2} = quantity;
    ProbeDat{k,3} = Probe_RL{k};
end
RefDat = cell(numel(Datenbank2),3);
for k = 1:numel(Datenbank2)
    % Break up individual entries using comma and whitespace delimiters
    parts = strsplit(Datenbank2{k},{',',' '});
    % Loop through components, getting element and quantity
    numParts = numel(parts);
    quantity = zeros(numParts-2,1); % preallocate
    element = cell(numParts-2,1); % preallocate
    for m = 1:numParts -2
        [quantity(m),element{m}] = numstr(parts{m});
    end
    group = str2double(parts{end});
    % Put into cell array
    RefDat{k,1} = element;
    RefDat{k,2} = quantity;
    RefDat{k,3} = group;
end
% Get alphabetically sorted list of all of the possible elements
allElements = sort(unique(vertcat(ProbeDat{:,1},RefDat{:,1})));
numVbls = numel(allElements); % total number of elements
% Make tables for probe data and databank (refernce) 
Probe = array2table(zeros(size(ProbeDat,1),numVbls),...
    'VariableNames',allElements);
Probe.Name = cell(height(Probe),1);
Probe = Probe(:,["Name";allElements]); % Make Name the first column
DataBank = array2table(zeros(size(RefDat,1),numVbls+1),...
    'VariableNames',['Group';allElements]);
% Loop through cell array assigning table row values to Probe table
% table has a column for each possible element, and entries with the
% composition. The leading column has the string name for the
% composition
for k = 1:height(Probe)
    for m = 1:numel(ProbeDat{k,1})
        Probe.(ProbeDat{k,1}{m})(k) = ProbeDat{k,2}(m);
    end
    Probe.Name{k} = ProbeDat{k,3};
    
end
% Loop through cell array assigning table row values to DataBank table
% table has a column for each possible element, and the entries with the
% composition, the leading column gives the group number for each
% composition
for k = 1:height(DataBank)
    for m = 1:numel(RefDat{k,1})
        DataBank.(RefDat{k,1}{m})(k) = RefDat{k,2}(m);
    end
    DataBank.Group(k) = RefDat{k,3};
end
disp(DataBank)

    Group     Ag      Al     Au     Ba      Bi      Ca      Cd     Co     Cr     Cu      Fe      K      La     Mg      Mn     Mo      Na     Ni     Pb      Sb      Si      Sr     Tl     U      V       W      Y       Zr 
    _____    ____    ____    __    ____    ____    _____    __    ____    __    ____    ____    ____    __    ____    ____    ___    ____    __    ____    ____    ____    ____    __    ___    ____    ___    ____    ____

     203        0       0    0        0       0     0.38    0        0    0        0    0.38       0    0        0       0      1       0    0        0       0       0       0    0       0       0      0       0       0
     204        0       0    0        0       0     0.25    0        0    0        0    0.38       0    0     0.13       0      1       0    0        0       0       0       0    0       0       0      0       0       0
     205        0       0    0        0       0     0.13    0        0    0        0    0.38       0    0        0       0      1    0.25    0        0       0       0       0    0       0       0      0       0       0
     206        0       0    0        0       1      0.5    0        0    0        0       0       0    0        0       0      0       0    0        0       0       0       0    0       0       0      0       0       0
     207        1       0    1        0       0        0    0        0    0      0.5       0       0    0        0       0      0       0    0      0.5       0       0       0    0       0       0      0       0       0
     208        0       0    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        0     0.5       0       0    1       0       0      0       0       0
     209        0       0    0        0       0        0    0        0    0        0       0       0    0     0.14       0      0    0.14    0        0       0       0       0    0       0       1      0       0       0
     210      0.5       0    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        1       0       0       0    0       0       0      0       0       0
     211        0       0    0        0       0        0    0        0    0        0       0       0    0        0       0    0.5       0    0        0       1       0       0    0       0       0      0       0       0
     212        0       0    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        0       0       0       0    0       1       0      0     0.5       0
     213        0       0    1        0       0        0    0        0    0     0.67       0       0    0        0       0      0       0    0     0.33       0       0       0    0       0       0      0       0       0
     214        0       0    0     0.17       0        0    0        0    0        0       0       0    0        0       0      0       0    0        0       0       0       0    0       1       0      0       0       0
     215        0       0    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        0       1       0       0    0       0       0    0.2       0       0
     216        0       0    0        0       0        0    0        0    0        0       1       0    0      0.2       0      0     0.2    0        0       0       0       0    0       0       0      0       0       0
     217        0       0    0        0       0        0    0        0    0        0     0.5       0    1        0       0      0       0    0        0       0       1       0    0       0       0      0       0       0
     218        0       0    0        0       0        0    1        0    0        1       0       0    0        0       0      0       0    0        0       0       0       0    0       0       0      0       0       0
     219        0       0    0        0       0     0.15    0        0    0        0       0    0.15    0        0       1      0    0.15    0        0       0       0       0    0       0       0      0       0       0
     220        0       0    0        0       0        1    0        0    0        0       0       0    0        0       0      0       0    0        0       0    0.47       0    0       0       0      0       0       0
     221        0       0    0        0     0.5        0    0        0    0        0       1       0    0        0       0      0       0    0        0       0       1       0    0       0       0      0       0       0
     222        0       0    0        0    0.22        0    0        0    0        0       0       0    0        0       0      0       0    1        0       0       0       0    0       0       0      0       0       0
     223        0       1    0        0       0        1    0        0    0        0       0       0    0        0       0      0       0    0        0    0.33       0       0    0       0       0      0       0       0
     224        0       1    0        0       0     0.33    0        0    0        0       0       0    0        0       0      0       0    0        0       0    0.67       0    0       0       0      0       0       0
     225        0       1    0      0.5       0        0    0        0    0        0       0       0    0        0       1      0       0    0        0       0       0       0    0       0       0      0       0       0
     226        0       0    0        0    0.17     0.33    0        0    0        1       0       0    0        0       0      0       0    0        0       0       0       0    0       0       0      0       0       0
     227        0       0    0        0       0        0    0        0    0        0       0     0.4    0      0.2       0      0       0    0        0       0       0       0    0       0       1      0       0       0
     228        0     0.5    0        0       0        0    0        0    0        0       0       0    0        0       0      0     0.5    0        0       0       0       0    0       1       0      0       0       0
     229        0     0.2    0        0       0        0    0        0    0        0     0.2       0    0        0       1      0     0.4    0        0       0       0       0    0       0       0      0       0       0
     230        0       0    0        0       0        0    0        0    0        0       0       0    0        0       0      0    0.13    0        0       0       0       0    0       1       0      0       0       0
     231        0     0.6    0        0       0        0    0        0    0      0.2       0       0    0        0       0      0       0    0      0.8       0       1       0    0       0       0      0       0       0
     232        0       0    0     0.13       0        0    0        0    0        0       0    0.13    0        0    0.88      0    0.38    0        0       0       1       0    0       0       0      0       0       0
     233        0       0    0        0       0     0.31    0        0    0        0       0       0    0        0       0      0    0.31    0        0       0       1    0.26    0       0       0      0    0.33    0.33
     234        0       0    1        0       0        0    0        0    0     0.33    0.33       0    0        0       0      0       0    0        1       0       0       0    0       0       0      0       0       0
     235        0       1    0        0       0        0    0        0    0        0       0       0    0        0       0      0       1    0        0       0       0       1    0       0       0      0       0       0
     236        0    0.25    0        0       0      0.1    0        0    0        0       0       0    0        0       0      0       0    0        0       0       1       0    0       0       0      0       0       0
     237        0       1    0      0.5       0        0    0        0    0        0       0       0    0        0       0      0     0.5    0        0       0       0    0.25    0       0       0      0       0       0
     238        0    0.16    0        0       0        0    0        0    0        0       1       0    0        0       0      0       0    0        0       0       0       0    0       0    0.96      0       0       0
     239     0.35       0    0        0       0        0    0        0    0     0.92       0       0    0        0       0      0       0    0        1       0       0       0    0       0       0      0       0       0
     240        0    0.86    0        0       0        0    0        0    0        0       0       0    0        0       0      0       1    0        0       0    0.86       0    0       0       0      0       0       0
     241        0       0    0        0       0        0    0        0    0        0       0       1    0        0       0      0       1    0        0       0       1       0    0       1       0      0       0       0
     242        0       1    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        0       0    0.13       0    0       0       0      0       0       0
     243        0       0    0     0.25       0        0    0        0    0        0       0       0    0        0       0      0    0.88    0        0       0       1       0    0       0       0      0       0       0
     244        0       0    0        0       0        0    0        0    0        1    0.25       0    0        0       0      0       0    0        0       0       0       0    0       0       0      0       0       0
     245     0.55       0    0        0       1        0    0        0    0        0       0       0    0        0       0      0       0    0        0       1       0       0    0       0       0      0       0       0
     246        0       1    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        0       0    0.22       0    0       0       0      0       0       0
     247        0    0.67    0        0       0        0    0        0    0        0       0    0.33    0        0       0      0       0    0        0       0       1       0    0       0       0      0       0       0
     248        0       0    0        0       0    0.875    0        0    0        0       0       0    0        0       0      0     0.5    0        0       0       1       0    0       0       0      0       0    0.13
     249        0    0.67    0        0       0        0    0        0    0        0     0.5       0    0     0.33       0      0    0.17    0        0       0       1       0    0       0       0      0       0       0
     250        0       0    0        0       0     0.17    0        0    0        0       0       0    0        0       1      0       0    0        0       0     0.5       0    0       0       0      0       0       0
     251        0       0    0        0       0        0    0        0    1        0    0.17       0    0     0.33       0      0       0    0        0       0       0    0.17    0       0       0      0       0       0
     252        0       0    0        0    0.43        0    0     0.79    0        0       1       0    0     0.79       0      0       0    0        0       0       0       0    0       0       0      0       0       0
     253        0       0    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        1     0.8       0       0    0       0       0      0       0       0
     254        0       0    0        0       0        0    0        0    0        1       0       0    0        0       0      0       0    0        1       1       0       0    0       0       0      0       0       0
     255        0       0    0        0       0        0    0        0    0        0       0       0    0        0       1      0     0.2    0        0       0       1       0    0       0       0      0       0       0
     256        0       0    0        0       0        0    0        0    0        0       0       0    0        0     0.5      0       0    0        1       0       0       0    0       0       1      0       0       0
     257        0       0    0        0       0        0    0        0    0        1       0       0    0        0       0      0    0.25    0        0       0       0       0    0       0       0      0       0       0
     258        0       0    0        0       0        0    0        0    0        1       0       0    0        0       0      0     0.2    0        0     0.2       0       0    0       0       0      0       0       0
     259        0       0    0        0       0      0.5    0        0    0        0       0       1    0        0       0      0       0    0        0       0       0       0    0     0.5       0      0       0       0
     260        0       0    0        0       0        0    0        0    0        0       0       0    0        0       1      0       0    0        0       0    0.14       0    0       0       0      0       0       0
     261        0       0    0        0       0        1    0        0    0        0       0       0    0     0.14       0      0       0    0        0       0    0.57       0    0       0       0      0       0       0
     262        0       0    0        0       1        0    0        0    0        0       1       0    0        0       0      0       0    0        1       0       0       0    0       0       0      0       0       0
     263        0    0.33    0        0       0        0    0        0    0        0       0       0    0        0       0      0       0    0        0       0       1    0.17    0       0       0      0       0       0
     264        0       0    0        0       0      0.5    0        0    0        0       0       0    0      0.5       0      0       1    0        0       0       0       0    0       0       0      0       0       0

% Loop through rows in Probe table looking for a matching group in DataRref
% and lableling accordingly
numCol = width(Probe); % number of columns before adding label
for k = 1:height(Probe)
    idl = ismembertol(DataBank{:,2:end},Probe{k,2:numCol},tol,...
        'DataScale',1,'ByRows',true);
    if any(idl)
        % Label the group, assume just one match or none
        Probe.Group(k) = DataBank.Group(idl);
    else
        Probe.Group(k) = NaN; % NaN for not found
    end
end
% Reorder the columns so that the group match is the second column, just to
% make it be more readable
Probe = Probe(:,['Name';'Group';allElements]);
disp(Probe)

            Name            Group    Ag    Al    Au    Ba    Bi    Ca    Cd    Co    Cr    Cu     Fe      K      La    Mg    Mn    Mo    Na    Ni    Pb    Sb     Si     Sr    Tl    U    V    W    Y    Zr
    ____________________    _____    __    __    __    __    __    __    __    __    __    __    ____    ____    __    __    __    __    __    __    __    __    ____    __    __    _    _    _    _    __

    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 
    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 
    {'1Fe,1Si'         }     NaN     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'0.91Si,1Fe'      }     NaN     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0     0.91    0     0     0    0    0    0    0 
    {'0.35Si,1Fe'      }     NaN     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0     0.35    0     0     0    0    0    0    0 
    {'0.19Fe,0.57K,1Si'}     NaN     0     0     0     0     0     0     0     0     0     0     0.19    0.57    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'0.47Fe,0.68K,1Si'}     NaN     0     0     0     0     0     0     0     0     0     0     0.47    0.68    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'1Si'             }     NaN     0     0     0     0     0     0     0     0     0     0        0       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'0.81Fe,1Si'      }     NaN     0     0     0     0     0     0     0     0     0     0     0.81       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 
    {'1Si'             }     NaN     0     0     0     0     0     0     0     0     0     0        0       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 
    {'0.44Fe,1Si'      }     NaN     0     0     0     0     0     0     0     0     0     0     0.44       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'1Na'             }     NaN     0     0     0     0     0     0     0     0     0     0        0       0    0     0     0     0     1     0     0     0        0    0     0     0    0    0    0    0 
    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 
    {'1Si'             }     NaN     0     0     0     0     0     0     0     0     0     0        0       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'0.43Fe,1Si'      }     NaN     0     0     0     0     0     0     0     0     0     0     0.43       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'0.6Si,1Fe'       }     NaN     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0      0.6    0     0     0    0    0    0    0 
    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 
    {'0.15Fe,1Si'      }     NaN     0     0     0     0     0     0     0     0     0     0     0.15       0    0     0     0     0     0     0     0     0        1    0     0     0    0    0    0    0 
    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 
    {'1Fe'             }     216     0     0     0     0     0     0     0     0     0     0        1       0    0     0     0     0     0     0     0     0        0    0     0     0    0    0    0    0 

[Tatjana Mü]

@Jon I have Matlab2019a.

[Jon]

When you run the example with just the three files I have attached do you get an error?

If so please copy and paste the entire (all of the red) error mesage

[Tatjana Mü]

@Jon Now it's working, can it happen that my original date are too big? for example in a normal case the sample has around 10.000 rows and the databank around 5.600 rows. The databank I was sending you, is just a little part of it.

If i use my correct databank I get this error:

Error using table.init (line 390)

The VariableNames property is a cell array of character vectors. To assign multiple variable names, specify non-blank names in a

string array or a cell array of character vectors.

Error in array2table (line 64)

t = table.init(vars,nrows,rownames,nvars,varnames);

Error in Test_Mineralcomposition (line 125)

Probe = array2table(zeros(size(ProbeDat,1),numVbls),...

[Jon]

I don't think that the sizes you mention would be a problem. If they were, you would get an "out of memory" error, not the error messages you described. When you run the code for the full scale problem do you now get errors? If so, are you sure you are running the exact same code as attached here, just a differnce in the .xlsx file sizes. If you do get such error messages, running this exact same code please copy and paste the entire error message. Otherwise, please replace your code with what is attached here, as we know this works.

[Tatjana Mü]

@Jon I really appreciate the time you spend to help me. I attached to original databank, with which I get an error message. Maybe you could check, if you also get the error.

[Jon]

I actually got a few errors. Two were due to issues with your Datenbank_Matlab_Gruppierung2.xlsx file. On line 8 there is a whitespace between 0.5 and Na which causes parsing problems. On line 1224 you have a comma where you should have a period, 0,67Ca instead of 0.67Ca, which also causes parsing problems. You either have to be really careful with the entries, or further modify the code to be more robust to such issues.

Once those are fixed, I found a more fundamental issue, which is that with the tolerance you specify, there may be more than one match in the database. In the attached code I select the closest match (minimum sum square ), so you don't have errors.

I have attached a corrected database .xlsx sheet, called Datenbank_Matlab_Gruppierung3.xlsx, and a modified Test_Mineralcomposition_v2.m which uses this database file, along with the the min square error match.

Finally, you may want to take a look at Group 742 in your database, it is listed as 0.25Fe,1Fe. Is this correct looks strange to have 0.25Fe and also 1Fe., also conflicts with Group 2285 which is pure Fe.

Also I noticed that you turn off the warnings at the top of your code, where you have the line warning off. I wouldn't recommend this setting. The warnings can give you useful information, and hints about what may be going wrong.

[Jon]

Here is a slightly modified version. The old one worked, but, depended upon the row numbers matching the group number. The logic here on labeling the group is better.

[Tatjana Mü]

THANK YOU SO MUCH!! I really appreciate your help. It's working perfectly

[Jon]

Your welcome. If you're going to use this code alot or sharing it with others you could even further improve it. Specifically, looking at the whole code I see that you first combine the mineral and composition name, and then later in the code that I gave you I split them apart again. Could do the processing I do after splitting them apart, while they are already separate at the beginning. Also as I mentioned you could check for badly placed spaces or punctuation (, instead of .) if others will be modifying the .xlsx databank file. Otherwise if the files aren't changing and you just need it to work you should be all set with what you have. Good luck with your project.