Need Row data after ismember or using strcmp (compare only single column A and later need all Row data of that particular column)

2 次查看(过去 30 天)
Hello Everyone
Here is 1st approach code 1: %%Here I want to find out 'aaa' from sheet->'MissingData', & later list out all Row data present on aaa column new sheet in same excel. This is done with writetable and readtable.
idx = ismember(tOld, tNew, 'rows'); %excels compared
writetable(tOld(~idx,:), 'SummaryResult.xlsx', 'Sheet', 'MissingData','Range','A2') %new sheet prepared with missing data
%%for finding 'aaa' i prepared dummy excel and compared with MissingData sheet
name = {'aaa'};
T = table(name);
writetable(T,'DummyTable.xlsx','sheet','temp');
B = readtable('DummyTable.xlsx','sheet','temp');
B = B(1:end,1:1);
A1 = readtable('SummaryResult.xlsx','sheet','MissingData'); %excels compared
A = A1(1:end,1:1);
idx = ismember(A, B, 'rows');
writeable(A(~idx,:),'SummaryResult.xlsx', 'Sheet', '724_MapDiff','Range','A2')
Second approach Code: Here the purpose is same but i used strcmp.
idx = ismember(tOld, tNew, 'rows'); %excels compared
writetable(tOld(~idx,:), 'SummaryResult.xlsx', 'Sheet', 'MissingData','Range','A2') %new sheet prepared with missing data
[num,txt,raw] = xlsread('SummaryResult.xlsx','NewlyAddedData');
in = 'aaa';
p = strcmp(in,raw(:,1)); % as 'aaa' is present in A column so.
if (p(:))
xlswrite('SummaryResult.xlsx',raw(p),'aaa','A2')
else
%do nothing
end
Please let me know for brief. I also have attached expected SummaryResult.xlsx. Pls look sheet3 and Sheet4.
Thank you

回答(1 个)

Voss
Voss 2022-7-23
Here is something that will read the given "data" sheets (in this case 'MissingData' and 'NewlyAddedData'), separate their contents according to the value in the first column (status), and write the data for each status to a new sheet with format more or less as given in the example file:
% read the input data sheets
file_name = 'SummaryResult.xlsx';
sheet_names = {'MissingData' 'NewlyAddedData'};
N_data = numel(sheet_names);
data = cell(1,N_data);
for ii = 1:N_data
data{ii} = readcell(file_name, ...
'Sheet',sheet_names{ii}, ...
'NumHeaderLines',1);
end
% make sure they have the same number of columns
Ncol_in = cellfun(@(x)size(x,2),data);
Ncol_out = max(Ncol_in);
idx = find(Ncol_in < Ncol_out);
for ii = idx(:).'
data{ii} = [data{ii} repmat({''},size(data{ii},1),Ncol_out-Ncol_in(ii))];
end
% get the set of unique statuses (column 1 of each)
status = cellfun(@(x)x(:,1),data,'UniformOutput',false);
status = unique(vertcat(status{:}));
% new sheet names are upper(status)
new_sheet_names = upper(status);
% write one new sheet for each status
N_new_sheets = numel(status);
% with 3 lines at the top (data name to be put in later)
section_header = repmat({''},3,Ncol_out);
% and 2 blank lines between sections
section_break = repmat({''},2,Ncol_out);
for jj = 1:N_new_sheets
% concatenate data for each new sheet
new_sheet_data = cell(0,Ncol_out);
% for each data section
for ii = 1:N_data
% put data name in section header
section_header{2,2} = sheet_names{ii};
% followed by data where column 1 is status{jj}
% followed by section break (2 blank lines)
new_sheet_data = [new_sheet_data; ...
section_header; ...
data{ii}(strcmp(data{ii}(:,1),status{jj}),:); ...
section_break];
end
% write the new sheet, overwriting anything that was already there
writecell(new_sheet_data,file_name, ...
'Sheet',new_sheet_names{jj}, ...
'WriteMode','overwritesheet');
end
% check the new sheets
for jj = 1:N_new_sheets
opts = detectImportOptions(file_name,'Sheet',new_sheet_names{jj});
opts.DataRange = 'A1';
disp(new_sheet_names{jj});
disp(readcell(file_name,opts));
end
AAA
{1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'MissingData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'aaa' } {'20220330_101314'} {[ 2400]} {[ 56.1000]} {'aaa' } {'20220330_143818'} {[ 2600]} {[ 60]} {'aaa' } {'20220330_101314'} {[ 2500]} {[ 56.1000]} {'aaa' } {'20220330_101316'} {[ 2700]} {[ 67.4000]} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'aaa' } {'20220330_143867'} {[ 1200]} {[ 89]}
BBB
{1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'MissingData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'bbb' } {'20220330_143818'} {[ 2200]} {[ 60]} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'bbb' } {'20220330_184684'} {[ 3400]} {[ 85]}
DDD
{1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {'MissingData' } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {'ddd' } {'20220330_143818'} {[2.4954e+03]} {[ 67.9000]}
EEE
{1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {'MissingData' } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {'eee' } {'20220330_184617'} {[3.7558e+03]} {[ 88.5000]}
Note that if a given status has no data (e.g., 'ddd' and 'eee' have no 'MissingData' in this case), then that data section in the sheet is still written but it contains only blank lines. To change the behavior so that those sections are not written at all, you can make a slight modification to the code, as follows:
for jj = 1:N_new_sheets
% concatenate data for each new sheet
new_sheet_data = cell(0,Ncol_out);
% for each data section
for ii = 1:N_data
% idx: logical index whether each row matches status{jj}
idx = strcmp(data{ii}(:,1),status{jj});
if ~any(idx)
% if no matches, don't write this section
continue
end
% put data name in section header
section_header{2,2} = sheet_names{ii};
% followed by data where column 1 is status{jj}
% followed by section break (2 blank lines)
new_sheet_data = [new_sheet_data; ...
section_header; ...
data{ii}(idx,:); ...
section_break];
end
% write the new sheet, overwriting anything that was already there
writecell(new_sheet_data,file_name, ...
'Sheet',new_sheet_names{jj}, ...
'WriteMode','overwritesheet');
end
% check the new sheets
for jj = 1:N_new_sheets
opts = detectImportOptions(file_name,'Sheet',new_sheet_names{jj});
opts.DataRange = 'A1';
disp(new_sheet_names{jj});
disp(readcell(file_name,opts));
end
AAA
{1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'MissingData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'aaa' } {'20220330_101314'} {[ 2400]} {[ 56.1000]} {'aaa' } {'20220330_143818'} {[ 2600]} {[ 60]} {'aaa' } {'20220330_101314'} {[ 2500]} {[ 56.1000]} {'aaa' } {'20220330_101316'} {[ 2700]} {[ 67.4000]} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'aaa' } {'20220330_143867'} {[ 1200]} {[ 89]}
BBB
{1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'MissingData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'bbb' } {'20220330_143818'} {[ 2200]} {[ 60]} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing} {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing} {1×1 missing} {'bbb' } {'20220330_184684'} {[ 3400]} {[ 85]}
DDD
{1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {'ddd' } {'20220330_143818'} {[2.4954e+03]} {[ 67.9000]}
EEE
{1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {1×1 missing} {'NewlyAddedData' } {1×1 missing } {1×1 missing} {1×1 missing} {1×1 missing } {1×1 missing } {1×1 missing} {'eee' } {'20220330_184617'} {[3.7558e+03]} {[ 88.5000]}
  1 个评论
Santosh Biradar
Santosh Biradar 2022-7-27
Thank you so much for your responce.
For for delayed resoponse.
It works for me.
I got a bit similar solution for the same.
Can You help with this?
%%raw is having data of excel size 10X20
%%SummaryResult.xlsx is excel havinnd "aaa" is sheet name
If aaa sheet contains initial with "Z" WORD then I will delete that ROW.
try
Index = find(contains(raw(1:numrow1,1:1),'Z'));
if (Index(:))
xlswrite('SummaryResult.xlsx',raw(~Index,:),'aaa','A2')   %%here I am trying to delete to delete those rows which is having Z letter
else
0;
end
end

请先登录,再进行评论。

产品


版本

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by