How to match two data columns?

5 次查看（过去 30 天）
Amr Hashem
Amr Hashem 2016-1-7
评论： Image Analyst 2016-1-11
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 个评论
显示 1更早的评论
Image Analyst
Image Analyst 2016-1-9
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 2016-1-10
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

请先登录，再进行评论。

请先登录，再回答此问题。

回答（2 个）

Image Analyst
Image Analyst 2016-1-7
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 2016-1-10
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 个评论
显示 2更早的评论
Amr Hashem
Amr Hashem 2016-1-10
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 2016-1-11
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Cell Arrays 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by