splitting and matching elements in excel

Hi,

I am trying to count the possible recipient matches in an excel file(attached). I have a donor and multiple recipients along with the state (healthy or unhealthy) in different columns. I want to count the state healthy when it corresponds with the elements in recipient’s column and the donor column as shown in the brown colored cells. If an element/s in the recipient column matches an element/s in set {12,13,14}, number of matches should be counted (only for donor =1)

For instance:

we have B2, C2, D2 fulfill the condition with 2 matches. 1 with 13, and 1 with 14

Another example, we have B5, C5, D5 fulfill the condition with 3 matches. 1 with 12, 1 with 13, and 1 with 14

I need to count all those matches. Any suggestions?

Thanks!!

Answer 1

[Guillaume]

desiredrecipients = [12 13 14];
desireddonor = 1;
desiredstate = 'Healthy';

t = readtable('TestHealthy.xls');  %read excel file
t.Recipients = rowfun(@(rec) str2double(strsplit(rec, ',')), t, 'InputVariables', 'Recipients', 'ExtractCellContents', true, 'OutputFormat', 'cell'); %split strings and convert to double
t.MatchedRecipients = rowfun(@(rec) sum(ismember(rec, desiredrecipients)), t, 'InputVariables', 'Recipients', 'ExtractCellContents', true, 'OutputFormat', 'uniform')  %count the number of matched recipients in each row
totalcount = sum(t.MatchedRecipients(t.Donor == desireddonor & strcmp(t.State, desiredstate)))

Answer 2

[aqua blue]

Thank you Guillaume!!

[Star Strider]

The most appropriate way to thank Guillaume is to Accept his answer.