Find the missing rows and newly added rows in two different excels sheets

Hello

I am comparing and finding out the missing rows and newly added rows data from two different excels sheets.

I have attached 3 files.

SummaryNew.xlsx and SummaryOld.xlsx are having data. Compare those 2 excel files.

• SummaryResult.xlsx In a result, I am expecting 2 sheets. MissingData and NewlyAddedData.

Thank you!!

I tried using

diff_rows = find(all(cellfun(@isequal, SummaryOld', SummaryNew')) == 0);

similar_rows = find(all(cellfun(@isequal, SummaryOld', SummaryNew')) == 1);

bt it is just comparing one excel whole with other.

Answer 1

[Akira Agata]

How about the following?

% Load the Excel files
tNew = readtable('https://jp.mathworks.com/matlabcentral/answers/uploaded_files/1069895/SummaryNew.xlsx',...
    'VariableNamingRule','preserve');
tOld = readtable('https://jp.mathworks.com/matlabcentral/answers/uploaded_files/1069900/SummaryOld.xlsx',...
    'VariableNamingRule','preserve');

% Newly Added data
idx = ismember(tNew, tOld, 'rows');
tNew(~idx,:)

ans = 2×4 table
    PLD Status       BLF File Name       Time(sec)    EgoSpeed(kmh)
    __________    ___________________    _________    _____________

     {'ddd'}      {'20220330_143818'}     2495.4          67.9     
     {'eee'}      {'20220330_184617'}     3755.8          88.5     

% Missing data
idx = ismember(tOld, tNew, 'rows');
tOld(~idx,:)

ans = 2×4 table
    PLD Status       BLF File Name       Time(sec)    EgoSpeed(kmh)
    __________    ___________________    _________    _____________

     {'aaa'}      {'20220330_101314'}      2101           56.1     
     {'bbb'}      {'20220330_143818'}      2200             60     

[Santosh Biradar]

Thank you So much Akira Agata for your responce and time.

This worked for me.

Now I just have to write this tOld and tNew data to new excel, inside two different sheets.

Thank you

[Akira Agata]

Oh, it's a piece of cake!

Please use writetable function, like:

% Load the Excel files
tNew = readtable('https://jp.mathworks.com/matlabcentral/answers/uploaded_files/1069895/SummaryNew.xlsx',...
    'VariableNamingRule','preserve');
tOld = readtable('https://jp.mathworks.com/matlabcentral/answers/uploaded_files/1069900/SummaryOld.xlsx',...
    'VariableNamingRule','preserve');

% Newly Added data -> sheet1
idx = ismember(tNew, tOld, 'rows');
writetable(tNew(~idx,:), 'SummaryResult.xlsx', 'Sheet', 1)

% Missing data -> sheet2
idx = ismember(tOld, tNew, 'rows');
writetable(tOld(~idx,:), 'SummaryResult.xlsx', 'Sheet', 2)

[Santosh Biradar]

As always very Thankful :). Big Help. Thank you so much Akira Agata!!

[Santosh Biradar]

Hello @Akira Agata

I have one more step need to add in the SummaryResult.

Your responce will be very helpful.

Eg.

In the SummaryResult Excel,

if A3 having value aaa and A6 also having aaa (like the same values present in A Column only) then Entire ROWS MissingData-sheet and NewlyaddedData-sheet will be written separately to new *AAA*Sheet in the Same SummaryResult.xlsx

if A4 having value bbb and A5 also bbb (the same values present in A Column only)then entire ROWS from MissingData-sheet and NewlyaddedData-sheet will be written separately to new *BBB* sheet in the Same SummaryResult.xlsx

I have attached upated Required SummaryResult.xlxs

Please have a look for ref. Sheet AAA and BBB

Thank you