I want to read the data columns titled Number and Code. Then use them as a single variable to count how many occurrences of that in total throughout the spreadsheet and write that down in another excel file.

Both columns I need to read have a collection of numbers and text. Read the Number and Code and then count how many of occurences are there. (There are different Numbers and different Codes for that same Number. (Each row indicates one data set.) Then write that information to a different excel file. For example,

Number Code Count

136-8522 A1 5

136-8522 A2 6

136-8522 B4 10

7398358 B5 15

7398358 G5 10

And so on for each data set.

Excel file is attached.

Answer 1

[Githin John]

Use the xlsread function to read in the raw data. You will see that some of the elements of the number column are numbers and some others string. You can use the cellfun function to convert the 'double' data tyes in the number column to string using the num2str function. Now you have the number column of type string. You can again use a cellfun function to concatenate the number and code values into a single string. Now convert this column of concatenated string values into a categorical table. This will fetch unique number-code pairs and the countcats function will give you the number of occurrences of each number-code pair.

Now the unique function can be used to obtain indices of the unique number-code pairs from the original column. This information of indices can be used to export to an excel file the number value, code, and number of occurences.

[~,~,A]=xlsread('Excel.xlsx');
B=A(:,[1,3]);
C=B(2:end,:);
c=cellfun(@Tostr,C(:,1),'UniformOutput',false);
C1=[c,C(:,2)];
W=cellfun(@Together,C1(:,1),C1(:,2),'UniformOutput',false);
t=table(W);
t.W=categorical(t.W);
[q,IW,Iq]=unique(W);


function f1=Together(a,b)
       f1=[a,b]; 
end

function f2=Tostr(c)
    if isa(class(c),'double')
        f2=num2str(c);
    elseif ischar(class(c))
        f2=num2str(c);
    end
end

[Janaka Prasanga]

Thank you for the help! Still I cannot figure out how to get the number of occurences for each Number+Code combination. Any idea? Thanks!

[Githin John]

The categorical will keep track of number of occurences. Use the commands categories(t.W) and countcats(t.W) to get the unique number+code combinations and their occurrences.