Split data based on column value to write in different sheets in excel
2 views (last 30 days)
Show older comments
1 45 45
1 34 46
2 53 57
3 34 83
2 34 86
3 23 46
need to write the above data in Sheet 1,2 and 3 in a excel by matching the value of first column.
Sheet 1
45 45
34 46
Sheet 2
53 57
34 86
sheet 3
34 83
23 46
0 Comments
Accepted Answer
Jon
on 6 Oct 2023
Edited: Jon
on 6 Oct 2023
You could do it like this
% Parameters
filename = "myfile.xlsx"
% Example data
A = [
1 45 45
1 34 46
2 53 57
3 34 83
2 34 86
3 23 46]
% Split data to different workbook sheets
for k = 1:max(A(:,1))
% Get submatrix for rows that match current index
Asplit = A(A(:,1)==k,2:end);
sheetName = "Sheet - " + num2str(k);
writematrix(Asplit,filename,'Sheet',sheetName)
end
4 Comments
Jon
on 6 Oct 2023
@Dyuman Joshi Ahh, good to know, about building strings using numbers directly. I hadn't seen that before, thanks!
More Answers (2)
Mathieu NOE
on 6 Oct 2023
hello
try this :
data = [1 45 45;
1 34 46;
2 53 57;
3 34 83;
2 34 86;
3 23 46];
for k =1:3
ind = data(:,1) == k; % check col number
data2export = data(ind,2:end); % select appropriate rows
% export to excel in separate sheets
%xlswrite('Result.xlsx',data2export,k); % option 1
writematrix(data2export,'Result.xlsx','Sheet',k); % option 2
end
Star Strider
on 6 Oct 2023
Using accumarray, one line to segment the matrix, then one loop to write the file sheets (and a second loop to verify the results).
Try this —
M = [1 45 45
1 34 46
2 53 57
3 34 83
2 34 86
3 23 46];
tic
Ms = accumarray(M(:,1), (1:size(M,1)), [], @(x){M(x,:)}); % Segment The Matrix By The First Column
toc
filename = 'Array.xlsx';
for k = 1:numel(Ms)
writematrix(Ms{k}(:,[2 3]), filename, 'Sheet',string(k))
end
for k = 1:numel(Ms)
A = readmatrix(filename, 'Sheet',string(k))
end
.
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!