create a new variable based on excel sheet values

24 views (last 30 days)
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

Accepted Answer

Stephen23
Stephen23 on 24 Mar 2022
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
  5 Comments
Stephen23
Stephen23 on 26 Mar 2022
Edited: Stephen23 on 26 Mar 2022
After the loop:
mat = horzcat(dat{:})

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!