Counting occurrence of matched strings in multiple columns

Hi:

Give that: TN = {Hex1}, DS= {Hex15, Hex16... Hex20}, Names= { Hi, Hello}. I want to count the occurrence of the matched strings from column A,B, and E of the string Hi with any of the elements in DS with the element in TN. I should have 2 occurrences as shown in the attached file (highlighted in Blue). Thanks!

Answer 1

[Akira Agata]

One possible solution would be like this.

TN = {'Hex1'};
DS = {'Hex15','Hex16','Hex17','Hex18','Hex19','Hex20'};

[~,txt] = xlsread('Myfile.xls');

idx =...
  ismember(txt(:,1),TN) &...
  ismember(txt(:,2),DS) &...
  strcmp(txt(:,5),'Hi');

The result is:

>> txt(idx,:)
ans =
2×5 cell array
  {'Hex1'}    {'Hex15'}    {0×0 char}    {0×0 char}    {'Hi'}
  {'Hex1'}    {'Hex20'}    {0×0 char}    {0×0 char}    {'Hi'}

[Haidi Bob]

Hi Akria

Thank you very much!

[Haidi Bob]

Hi :

I have another question in the same topic. The problem is when having cells with numeric values separated by commas. Assume we have DS = {'2','3','4','7','8','9','12','13','14','18'}. We have, in the new attached file, two Holas, but in column B the intersection between the elements in B6 and DS is equal to zero (no common elements). The cell B1 is the only one that should be counted since we have intersection between B1 and DS, So we have one occurrence. Thank you again.

[Akira Agata]

Hmm, a little bit complicated. But it's doable. Here is one possible try.

DS = {'2','3','4','7','8','9','12','13','14','18'};
[~,txt] = xlsread('Myfile2.xls');
func = @(x) ~isempty(intersect(DS,split(x,',')));
idx = cellfun(func,txt(:,2)) & strcmp(txt(:,5),'Hola');

The result is:

>> txt(idx,:)
ans =
1×5 cell array
  {0×0 char}    {'1,2,13,14,18'}    {0×0 char}    {0×0 char}    {'Hola'}

[Haidi Bob]

Thanks a lot. the 3rd line is really tricky, but it works.

[Haidi Bob]

Dear Akira:

Thank you for your responses.

I have a question regarding the first post. I am using, TN and DS as decimal values, which needs to be converted to a hexadecimal form like this " fe80::c30c:0:0: one element of the DS or TN here. "

One more thing, there is a time column, let say column 1,and shift the other columns by 1, and search as you mentioned in post 1.

I can simply put every element in the aforementioned structure directly, but I thought you might propose a neater form.

Thanks.