How to match two data columns?

7 views (last 30 days)
Amr Hashem
Amr Hashem on 7 Jan 2016
Commented: Image Analyst on 11 Jan 2016
I have 2 excel sheets (each contain two columns( Serial & ID) 1st sheet(3708*2) 2nd sheet(2360*2))
both has the same Serial data but not arranged.
I want to fill each ID cell (in sheet 2) with each corresponding ID cell(in sheet 1) by using Serial column.
(copy ID from 1 to 2)
I use this code:
[ndata2 text2 alldata2] = xlsread('Basic.xlsx','WorksheetID4');
[ndata text alldata] = xlsread('Database.xlsx','WorksheetW4');
[R1,C1]=size(alldata2);
[R2,C2]=size(alldata);
colsize=max(C1,C2);
for j=1:3708
for i=1:2360 %length(alldata)
if alldata{j,1}== alldata2{i,1}
alldata{j,2}=alldata2{i,2};
end
end
i=0
end
newdata=alldata;
xlswrite('Database.xlsx',newdata,4);
but I got an error:
matrix dimensions must agree
if alldata{j,1}== alldata2{i,1}
note: some Serial data include text (ex.: 3XGE0013 or N123)
How to match the two data?
  3 Comments
Image Analyst
Image Analyst on 9 Jan 2016
Try using ismember() to identify rows that you want to delete. I still think using readtable and then innerjoin and outerjoin will do it for you. Not sure why you didn't try my suggestion. Of course I can't even try anything because you didn't attach any data.
Amr Hashem
Amr Hashem on 10 Jan 2016
I try :
out = all(ismember(DO,D),2);
but I got an error:
Input A of class cell and input B of class cell must be cell
arrays of strings, unless one is a string.
here is the file of data, if you can help
Thanks in advance

Sign in to comment.

Answers (2)

Image Analyst
Image Analyst on 7 Jan 2016
DO NOT call your text variable text, since that is the name of a built-in function. Also, try using readtable() instead of xlsread() - then you can use commands built just for this such as innerjoin and outerjoin.

Image Analyst
Image Analyst on 10 Jan 2016
Try it this way:
tWith = readtable('serial.xlsx', 'Filetype', 'Spreadsheet', 'Sheet', 'with ID')
tWithout = readtable('serial.xlsx', 'Filetype', 'Spreadsheet', 'Sheet', 'without ID')
[Lia, Locb] = ismember(tWith(:, 1), tWithout(:, 1))
% Lia is the rows of tWith that are found somewhere in tWithout.
% If A and B are tables, then Locb contains the lowest index in B
% for each row in A that is also a row in B.
% The output vector, Locb, contains 0 whenever A is not a row of B.
aIndexes = find(Lia);
Locb(Locb==0) = []; % Get rid of indexes of 0
newWith = tWithout;
% Transfer columns 2
newWith{Locb,2} = tWith{aIndexes, 2}
  4 Comments
Amr Hashem
Amr Hashem on 10 Jan 2016
when i use xlsread(), I got an error:
Input A of class cell and input B of class cell must be cell
arrays of strings, unless one is a string.
so I use:
[ndata2 text2 alldata2] = xlsread('serial.xlsx','with ID');
[ndata text1 alldata] = xlsread('serial.xlsx','without ID');
tWith=ndata2;
tWithout=ndata;
[Lia, Locb] = ismember(tWith(:, 1), tWithout(:, 1));
aIndexes = find(Lia);
newWith = tWithout;
newWith{nLocb,2} = tWith{naIndexes, 2}
but also I got an error:
Cell contents reference from a non-cell array object.
at
newWith{nLocb,2} = tWith{naIndexes, 2}
Image Analyst
Image Analyst on 11 Jan 2016
I guess only alldata, alldata2, text2, and text1 will be cell arrays and ndata2 and ndata will be regular numerical arrays, so use parentheses instead of braces.

Sign in to comment.

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!