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

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

Answer 1

[Jon]

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

[Jon]

Sorry, looks like @Mathieu NOE already provided a similar answer, while I was posting this one

[Dyuman Joshi]

You can directly join numbers to strings -

"Sheet - " + 1

ans = "Sheet - 1"

"Sheet - " + (1:3)

ans = 1×3 string array
    "Sheet - 1"    "Sheet - 2"    "Sheet - 3"

[Jon]

@Dyuman Joshi Ahh, good to know, about building strings using numbers directly. I hadn't seen that before, thanks!

[Dyuman Joshi]

You are welcome!

Answer 2

[Mathieu NOE]

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

[MATHANA GOPAL A]

Thank you for your timely advise. your idea also fit for my need. thank you so much.

Answer 3

[Star Strider]

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

.