MATLAB: How to delete rows based on values in the first columns that not exist in both data sheets

Question

Hey guys, I am new to matlab and I have been struggling for a while. I have two different large datasheets which i want to align based on the values in the first colums which are countries. The rows with countries that not exist in both sheets should be removed. For example

Sheet1

'AT' 5000 2000 4000

'BE' 6000 4000 5000

'BG' 5000 2000 4000

'CH' 5000 5000 4000

'CY' 6000 4000 5000

Sheet2

'AT' 8000 5000 3000

'BE' 4000 2000 3000

'GR' 5000 2000 4000

'HU' 7000 9000 8000

'CY' 7000 9000 9000

The end- result should be like this:

Sheet1

'AT' 5000 2000 4000

'BE' 6000 4000 5000

'CY' 6000 4000 5000

and sheet2

'AT' 8000 5000 3000

'BE' 4000 2000 3000

'CY' 7000 9000 9000

I have tried it by seperating the countries first from the sheets and then use the unique function to obtain the countries that exist in both files:

Sheet1_C= Sheet1(1:end, 1)

Sheet2_C=Sheet2(1:end, 1)

Countries = [Sheet1_C; Sheet2_C];

Unique_Countries = unique(Countries);

But now the country list that i have left should be aligned back with the data and that is what causes me a lot of trouble.

I hope someone can head me in the right direction! Thank you in advance!

Es

Answer 1

Note: x(1:end, 1) is written more simply as x(:, 1).

To answer your question, and assuming that the countries are only present once in each sheet:

[~, rSheet1, rSheet2] = intersect(Sheet1(:, 1), Sheet2(:, 1));
Reduced_Sheet1 = Sheet1(rSheet1, :);
Reduced_Sheet2 = Sheet2(rSheet2, :);