Intersect, Compare two columns or two matrix and extract common information

Hi All,

I have a matrixe with 4 columns (A to D) or 1 to 4. I want to compare columns 2 and 4 and if a match is found, put the entire corresponding rows in a separate matrix called Match. Ex ample when it comes to row 2, under coulms 2 (B) AND 4 (D), the vaule 12.1 is common to both. Hence put this entire matching row in a new matrix which will become 12, 12.1, 12.4, 12.1

The idea is to extract information common to both columns 2 and 4, for each row and after finding this common information, include with them their associated information in coulmns 1 and 3. Please see attached im

I tried using the intersect function but it doesnt work the way I want it to.

Thanks very much.

Answer 1

[Adam Danz]

Here's a demo you can follow. It identifies rows of a matrix 'data' where the value in column 2 exactly matches the value in column 4.

% demo, random data
data = randi(3,12,4); 

% Find rows where value in col 2 exactly matches value in col 4
isMatch = data(:,2)==data(:,4);
% Put those rows into a new matrix
newMat = data(isMatch,:); 

For floating decimals, 4.9999999 and 4.9999998 will not be an exact match. You can subtract the values from columns 2 and 4 and determine if the absolute value of their difference is less than some threshold you choose.

% Find rows where value in col 2 exactly matches value in col 4
isMatch = abs(data(:,2)-data(:,4)) < 0.0001; 
% Put those rows into a new matrix
newMat = data(isMatch,:);

[Curious Mind]

Thanks Adam for your response. What happens if I have 2 different data or matrices, A and B and I am comparing the second columns in each matrix to each other? Also will the code work if one matrix is longer than the other? Such as A being an 6*2 matrix and B being an 8*2 matrix. Thanks you!

[Adam Danz]

"What happens if I have 2 different data or matrices, A and B and I am comparing the second columns in each matrix to each other?"

% For exact matches, instead of 
isMatch = data(:,2)==data(:,4);

% use
isMatch = A(:,2)==B(:,2);

% For floating decimal, instead of 
isMatch = abs(data(:,2)-data(:,4)) < 0.0001; 

% use
isMatch = abs(A(:,2)-B(:,2)) < 0.0001; 

"Also will the code work if one matrix is longer than the other?"

This approach will not work if the matrices have a different number of rows. If matrix A has 6 rows and matrix B has 8 rows, which rows should compared? One way around this is to add rows of NaN to the shorter matrix.

A = rand(6,2);
B = rand(8,2); 
 
A = [A;nan(size(B,1)-size(A,1),size(A,2))]