Split data based on column value to write in different sheets in excel

2 vues (au cours des 30 derniers jours)
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

Réponse acceptée

Jon
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
Jon le 6 Oct 2023
@Dyuman Joshi Ahh, good to know, about building strings using numbers directly. I hadn't seen that before, thanks!
Dyuman Joshi
Dyuman Joshi le 6 Oct 2023
You are welcome!

Connectez-vous pour commenter.

Plus de réponses (2)

Mathieu NOE
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
  1 commentaire
MATHANA GOPAL A
MATHANA GOPAL A le 6 Oct 2023
Thank you for your timely advise. your idea also fit for my need. thank you so much.

Connectez-vous pour commenter.


Star Strider
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
Elapsed time is 0.003118 seconds.
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
A = 2×2
45 45 34 46
A = 2×2
53 57 34 86
A = 2×2
34 83 23 46
.

Catégories

En savoir plus sur Matrix Indexing dans Help Center et File Exchange

Produits


Version

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by