Comparing data with numbers and text

Hello everyone,

I have files that have both text and numbers. See below. I am trying to determine which rows match for columns 2 and 3. For instance, FILE1 (1 5012 X) would match FILE2 (7 5012 X). The corresponding indices would be i_FILE1 = 1 and i_FILE2 = 7. I tried using the function intersect, but it doesn't seem to work. Is there any way to do this? Note that I load in the data as cell arrays. If there is a better way, then let me know. Thanks.

FILE1

    1    5012   X 
    2    5012   Y 
    3    5012   Z 
    4    5012  RX 
    5    5012  RY 
    6    5012  RZ 
    7    5013   X 
    8    5013   Y 
    9    5013   Z 
   10    5013  RX 
   11    5013  RY 
   12    5013  RZ 
   13    5021   X 
   14    5021   Y 
   15    5021   Z 
   16    5021  RX 
   17    5021  RY 
   18    5021  RZ

FILE2

    1    9800   X 
    2    9800   Y 
    3    9800   Z 
    4    9800  RX 
    5    9800  RY 
    6    9800  RZ 
    7    5012   X 
    8    5012   Y 
    9    5012   Z 
   10    5012  RX 
   11    5012  RY 
   12    5012  RZ 
   13    5013   X 
   14    5013   Y 
   15    5013   Z 
   16    5013  RX 
   17    5013  RY 
   18    5013  RZ 
   19    5021   X 
   20    5021   Y 
   21    5021   Z 
   22    5021  RX 
   23    5021  RY 
   24    5021  RZ

Best regards,

Bill Rooker

[Matt Fig]

Please show how the data actually looks. Is it like this:

   FILE1 = {'1 5012 X';'2 5012 Y';' 3 5012 Z'}% etc.

or what?

[Bill Rooker]

currently, I am copying the date from the file and pasting into the Workspace. It generates a cell array with size of N by 3 where columns 1 and 2 are numbers and column 3 is text.

[per isakson]

How did you try intersect?

Answer 1

[per isakson]

One possibility:

    F1 = {
    '5012_X'
    '5012_Y'
    '5012_Z'
    '5012_RX'
    '5012_RY'
    '5012_RZ'
    '5013_X'
    '5013_Y'
    '5013_Z'
    '5013_RX'
    '5013_RY'
    '5013_RZ'
    '5021_X'
    '5021_Y'
    '5021_Z'
    '5021_RX'
    '5021_RY'
    '5021_RZ'};

    F2 = {
    '9800_X'
    '9800_Y'
    '9800_Z'
    '9800_RX'
    '9800_RY'
    '9800_RZ'
    '5012_X'
    '5012_Y'
    '5012_Z'
    '5012_RX'
    '5012_RY'
    '5012_RZ'
    '5013_X'
    '5013_Y'
    '5013_Z'
    '5013_RX'
    '5013_RY'
    '5013_RZ'
    '5021_X'
    '5021_Y'
    '5021_Z'
    '5021_RX'
    '5021_RY'
    '5021_RZ' };
    [C,ia,ib] = intersect( F1, F2 );

[Bill Rooker]

This might work. I assume that I should combine the 2 columns together. I need to do this using code since there are 1000s of rows to compare.

Looks like I would convert the number into a string. Then, I would combine them together. See below for the code. If I use this technique, it appears that I will have to loop over each row to combine it together into a cell array. If there is a better way, please let me know.

    F1 = {[num2str(FILE1{1,2}),'_',strtrim(FILE1{1,3})]};

[per isakson]

I prefer to use sprintf:

    F1 = cell( N, 1 ); 
    for ii = 1 : N
        F1{ii} = sprintf( '%u_%s', FILE1{ii,2}, FILE1{ii,3} );
    end