Need to compare two excel and extract the mismatch data
6 次查看(过去 30 天)
显示 更早的评论
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 .... !!!!
3 个评论
Alex Mcaulley
2020-3-9
setxor could be another option. As @darova says show us your code and your specific issues to obtain better answers
回答(1 个)
Nihal
2024-6-19
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
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!