How to match two data columns?

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?

[Amr Hashem]

it works when I remove all the numbers SN which contain strings (ex: N1234) from the second sheet (alldata)

 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
     end

How I could use the code with cells which contain combination of text and number:

n12345, not available, NA, 111-134, 23(1234)

any suggestion.

[Image Analyst]

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]

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

Answer 1

[Image Analyst]

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.

Answer 2

[Image Analyst]

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}

[Amr Hashem]

I got an error:

 Undefined function 'readtable' for input arguments of type
'char'.

I am using matlab R2012a

[Image Analyst]

Wow - that's old. Time to upgrade. Though the code should work with cell arrays too, which you get if you use xlsread().

[Amr Hashem]

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]

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.