Need to compare two excel and extract the mismatch data
6 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
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 comentarios
Alex Mcaulley
el 9 de Mzo. de 2020
setxor could be another option. As @darova says show us your code and your specific issues to obtain better answers
Respuestas (1)
Nihal
el 19 de Jun. de 2024
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 comentarios
Ver también
Categorías
Más información sobre Data Import from MATLAB en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!