Split data based on column value to write in different sheets in excel
3 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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 commentaires
Réponse acceptée
Jon
le 6 Oct 2023
Modifié(e) : Jon
le 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 commentaires
Jon
le 6 Oct 2023
@Dyuman Joshi Ahh, good to know, about building strings using numbers directly. I hadn't seen that before, thanks!
Plus de réponses (2)
Mathieu NOE
le 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
le 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 commentaires
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!