Need to compare two excel and extract the mismatch data

I have excel Conatant_Sheet1 in that I have two coloumns like signal name and the conatant value.

I have excel 2 name Constant_sheet2 in that again the same data but count is mismatched.

Suppose if in sheet1 we have 10 conatants then there will be 8 conatnts in sheet2 I need to comapre these names along with the values and extarct the names of the signals which are having the differences.

I have attached two sample excel sheets here. Expected results will be like the mismatched data

as Conatant You is added in the sheet2 and value of conatant Barak_obama is chnaged so this is again one mismatch

I want this two misamtched signal names along with their values in the output( whereever in new excel or in matlab variable ..any possible ways)

Mismatched data :

Barak_Obama 4

You 5

Thanks in Advance .... !!!!

[darova]

Can you show something? Did you try strcmp function?

[Alex Mcaulley]

setxor could be another option. As @darova says show us your code and your specific issues to obtain better answers

[Pratik Yadav]

I didnt prepared any code. But I serached that it can be done with the help of spreadsheet comapre. So can we trigger spreadsheet compare software through matlab,just like we can trigger excel though matlab

Answer 1

[Nihal]

I understand you're looking to compare two Excel sheets for differences in signal names and their corresponding values, then extract any mismatches. While I can't directly access files or attachments, I can guide you through how to accomplish this task in MATLAB.

Here's a step-by-step approach:

Step 1: Read the Excel Sheets

You'll start by reading the data from both Excel sheets into MATLAB.

% Replace 'path_to_Constant_Sheet1.xlsx' and 'path_to_Constant_Sheet2.xlsx' with your actual file paths
[dataSheet1, txtSheet1, ~] = xlsread('path_to_Constant_Sheet1.xlsx');
[dataSheet2, txtSheet2, ~] = xlsread('path_to_Constant_Sheet2.xlsx');
% Assuming the first column is text (signal names) and the second column is numeric (constant values)
signalNames1 = txtSheet1(2:end, 1); % Adjust the indexing based on your actual data layout
constantValues1 = dataSheet1(:, 1); % Assuming values are in the first column
signalNames2 = txtSheet2(2:end, 1);
constantValues2 = dataSheet2(:, 1);

Step 2: Compare and Find Mismatches

Now, compare the two sets of data to find mismatches in signal names and their values.

mismatchedSignals = {}; % Initialize a cell array to store the results
for i = 1:length(signalNames1)
    idx = find(strcmp(signalNames1{i}, signalNames2));
    if isempty(idx)
        % Signal name does not exist in sheet2
        mismatchedSignals = [mismatchedSignals; {signalNames1{i}, constantValues1(i), ""}];
    else
        if constantValues1(i) ~= constantValues2(idx)
            % Signal name exists but values differ
            mismatchedSignals = [mismatchedSignals; {signalNames1{i}, constantValues1(i), constantValues2(idx)}];
        end
    end
end

% Check for any signals in sheet2 that are not in sheet1
for i = 1:length(signalNames2)
    if ~any(strcmp(signalNames2{i}, signalNames1))
        mismatchedSignals = [mismatchedSignals; {signalNames2{i}, 'N/A', constantValues2(i)}];
    end
end

Step 3: Display or Save the Results

You can display the results in MATLAB's Command Window, or save them to a new Excel file.

% Display in Command Window
disp('Mismatched data:');
disp(mismatchedSignalNames);
% Save to a new Excel file
xlswrite('MismatchedSignals.xlsx', [{'Signal Name', 'Value in Sheet1', 'Value in Sheet2'}; mismatchedSignals]);

This code snippet assumes your Excel sheets are structured with signal names in the first column and their corresponding values in the second column, starting from the second row. Adjust the indexing and column selections as necessary to match your data's layout.

Remember to replace `'path_to_Constant_Sheet1.xlsx'` and `'path_to_Constant_Sheet2.xlsx'` with the actual paths to your Excel files.

Here is the full code in single snippet:

% Read the Excel Sheets
% Replace 'path_to_Constant_Sheet1.xlsx' and 'path_to_Constant_Sheet2.xlsx' with your actual file paths
[dataSheet1, txtSheet1, ~] = xlsread('Constant_Sheet2.xlsx');
[dataSheet2, txtSheet2, ~] = xlsread('Constant_Sheet1.xls');

% Assuming the first column is text (signal names) and the second column is numeric (constant values)
signalNames1 = txtSheet1(2:end, 1); % Adjust the indexing based on your actual data layout
constantValues1 = dataSheet1(:, 1); % Assuming values are in the first column

signalNames2 = txtSheet2(2:end, 1);
constantValues2 = dataSheet2(:, 1);

% Compare and Find Mismatches
mismatchedSignals = {}; % Initialize a cell array to store the results

for i = 1:length(signalNames1)
    idx = find(strcmp(signalNames1{i}, signalNames2));
    if isempty(idx)
        % Signal name does not exist in sheet2
        mismatchedSignals = [mismatchedSignals; {signalNames1{i}, constantValues1(i),""}];
    else
        if constantValues1(i) ~= constantValues2(idx)
            % Signal name exists but values differ
            mismatchedSignals = [mismatchedSignals; {signalNames1{i}, constantValues1(i), constantValues2(idx)}];
        end
    end
end

% Check for any signals in sheet2 that are not in sheet1
for i = 1:length(signalNames2)
    if ~any(strcmp(signalNames2{i}, signalNames1))
        mismatchedSignals = [mismatchedSignals; {signalNames2{i}, 'N/A', constantValues2(i)}];
    end
end

% Display or Save the Results
% Display in Command Window
disp('Mismatched data:');
disp(mismatchedSignals);

% Save to a new Excel file
xlswrite('MismatchedSignals.xlsx', [{'Signal Name', 'Value in Sheet1', 'Value in Sheet2'}; mismatchedSignals]);

I hope it helps