create a new variable based on excel sheet values

I wanted to import data from excel spread sheets from multiple sheets. So i used a loop

[~,sheet_name]=xlsfinfo('filename.xlsx')
for k=1:numel(sheet_name)
    data{k}=xlsread('filename.xlsx',sheet_name{k})
end

Each sheets in the excel file has 5, 3, 4 column data (example)

I wanted to create two variables new_variable1 and new_variable2

When I read the excel sheets itself, how many columns the excel sheet has, I want to fill the new_variable1 with value 1, when reading the next sheet fill the new_variable1 with 2, and so on

So as per the example my new_variable1 will have values

new_variable1 = [ 1 1 1 1 1 2 2 2 3 3 3 3];

Similarly, when I read the excel sheets itself, how many columns the excel sheet has, I want to fill the new_variable2 with value 1 in the first row, when reading the next sheet fill the new_variable1 with 1 in the second row, and so on

So as per example, at last the values in new_variable2 will be

new_variable2 =

1     1     1     1     1     0     0     0     0     0     0     0
0     0     0     0     0     1     1     1     0     0     0     0
0     0     0     0     0     0     0     0     1     1     1     1

Please can someone help me write the code to get values in new_variable1 and new_variable2

Answer 1

[Stephen23]

fnm = 'test.xlsx';
snm = sheetnames(fnm)

snm = 3×1 string array
    "Sheet1"
    "Sheet2"
    "Sheet3"

dat = cell(size(snm));
for k = 1:numel(dat)
    dat{k} = readmatrix(fnm,'Sheet',snm{k});
end
%
nmc = cellfun(@width,dat);
v1 = repelem(1:numel(dat),nmc)

v1 = 1×12

1 1 1 1 1 2 2 2 3 3 3 3

v2 = v1==(1:numel(dat)).'

v2 = 3×12 logical array
   1   1   1   1   1   0   0   0   0   0   0   0
   0   0   0   0   0   1   1   1   0   0   0   0
   0   0   0   0   0   0   0   0   1   1   1   1

[Elysi Cochin]

Sir, i got this error

Error using cellfun
Check for missing argument or incorrect argument data type in call to function 'width'.
nmc = cellfun(@width,dat);

I am using Matlab version 2020a, also sir my data is .xls format

so will readmatrix work for .xls format

[Stephen23]

"so will readmatrix work for .xls format"

Yes.

"i got this error"

From R2020b WIDTH also works on arrays, not just on tables. For your version we could use:

nmc = cellfun('size',dat,2);

[Elysi Cochin]

Sir one more doubt, what if i want to concatenate all the excel sheet values into one matrix variable instead of stoting it in a cell variable? What is the change i need to do?

[Stephen23]

After the loop:

mat = horzcat(dat{:})

[Elysi Cochin]

thank you sir