How to find matching string and re-arrange the table

Hi,

I want the "Final resulting table". I have Country and names, and NameDataList (names and data). I want add country names to "NameDataList" so as I want to get 'Final resulting table'. Can someone kindly help me how to get Final resulting table from the other two tables. Many many thanks in advance.

CountyNameList:

    Country1    Name1
    Country1    Name3
    Country1    Name6
    Country1    Name7
    Country2    Name9
    Country2    Name12
    Country2    Name14
    Country3    Name18
    Country3    Name19
    Country3    Name21
    Country4    Name25
    Country4    Name31
    Country4    Name40

NameDataList:

Name1   0.1
Name6   0.3
Name1   0.2
Name3   0.3
Name3   0.4
Name6   0.3
Name1   0.2
Name1   0.5
Name6   0.4
Name7   0.2
Name3   0.3
Name1   0.6
Name9   0.5
Name9   0.4
Name12  0.1
Name12  0.3
Name14  0.4
Name14  0.4
Name14  0.4
Name18  0.5
Name18  0.4
Name18  0.6  
Name14  0.4
Name18  0.5
Name19  0.4
Name19  0.6 
Name14  0.4
Name19  0.5
Name19  0.4
Name1   0.6 
Name21  0.4
Name21  0.5
Name31  0.4
Name40  0.6 
Name14  0.4
Name21  0.5
Name31  0.4
Name25  0.6 
Name14  0.4
Name40  0.5
Name31  0.4
Name25  0.6

Final resulting table I want:

Country1  Name1   0.1
Country1  Name6   0.3
Country1  Name1   0.2
Country1  Name3   0.3
Country1  Name3   0.4
Country1  Name6   0.3
Country1  Name1   0.2
Country1  Name1   0.5
Country1  Name6   0.4
Country1  Name7   0.2
Country1  Name3   0.3
Country1  Name1   0.6
Country2  Name9   0.5
Country2  Name9   0.4
Country2  Name12  0.1
Country2  Name12  0.3
Country2  Name14  0.4
Country2  Name14  0.4
Country2  Name14  0.4
Country3  Name18  0.5
Country3  Name18  0.4
Country3  Name18  0.6
Country2  Name14  0.4
Country3  Name18  0.5
Country3  Name19  0.4
Country3  Name19  0.6
Country2  Name14  0.4
Country3  Name19  0.5
Country3  Name19  0.4
Country1  Name1   0.6
Country3  Name21  0.4
Country3  Name21  0.5
Country4  Name31  0.4
Country4  Name40  0.6
Country2  Name14  0.4
Country3  Name21  0.5
Country4  Name31  0.4
Country4  Name25  0.6
Country2  Name14  0.4
Country4  Name40  0.5
Country4  Name31  0.4
Country4  Name25  0.6

Answer 1

[Stephen23]

Try this:

>> CNL = {'Country1','Name1';'Country1','Name3';'Country1','Name6';'Country1','Name7';'Country2','Name9';'Country2','Name12';'Country2','Name14';'Country3','Name18';'Country3','Name19';'Country3','Name21';'Country4','Name25';'Country4','Name31';'Country4','Name40'};
>> NDL = {'Name1',0.1;'Name6',0.3;'Name1',0.2;'Name3',0.3;'Name3',0.4;'Name6',0.3;'Name1',0.2;'Name1',0.5;'Name6',0.4;'Name7',0.2;'Name3',0.3;'Name1',0.6;'Name9',0.5;'Name9',0.4;'Name12',0.1;'Name12',0.3;'Name14',0.4;'Name14',0.4;'Name14',0.4;'Name18',0.5;'Name18',0.4;'Name18',0.6;'Name14',0.4;'Name18',0.5;'Name19',0.4;'Name19',0.6 ;'Name14',0.4;'Name19',0.5;'Name19',0.4;'Name1',0.6 ;'Name21',0.4;'Name21',0.5;'Name31',0.4;'Name40',0.6 ;'Name14',0.4;'Name21',0.5;'Name31',0.4;'Name25',0.6 ;'Name14',0.4;'Name40',0.5;'Name31',0.4;'Name25',0.6};

>> X = cellfun(@(c)find(strcmp(c,CNL(:,2))),NDL(:,1));
>> out = [CNL(X,1),NDL]
out = 

    'Country1'    'Name1'     [0.1000]
    'Country1'    'Name6'     [0.3000]
    'Country1'    'Name1'     [0.2000]
    'Country1'    'Name3'     [0.3000]
    'Country1'    'Name3'     [0.4000]
    'Country1'    'Name6'     [0.3000]
    'Country1'    'Name1'     [0.2000]
    'Country1'    'Name1'     [0.5000]
    'Country1'    'Name6'     [0.4000]
    'Country1'    'Name7'     [0.2000]
    'Country1'    'Name3'     [0.3000]
    'Country1'    'Name1'     [0.6000]
    'Country2'    'Name9'     [0.5000]
    'Country2'    'Name9'     [0.4000]
    'Country2'    'Name12'    [0.1000]
    'Country2'    'Name12'    [0.3000]
    'Country2'    'Name14'    [0.4000]
    'Country2'    'Name14'    [0.4000]
    'Country2'    'Name14'    [0.4000]
    'Country3'    'Name18'    [0.5000]
    'Country3'    'Name18'    [0.4000]
    'Country3'    'Name18'    [0.6000]
    'Country2'    'Name14'    [0.4000]
    'Country3'    'Name18'    [0.5000]
    'Country3'    'Name19'    [0.4000]
    'Country3'    'Name19'    [0.6000]
    'Country2'    'Name14'    [0.4000]
    'Country3'    'Name19'    [0.5000]
    'Country3'    'Name19'    [0.4000]
    'Country1'    'Name1'     [0.6000]
    'Country3'    'Name21'    [0.4000]
    'Country3'    'Name21'    [0.5000]
    'Country4'    'Name31'    [0.4000]
    'Country4'    'Name40'    [0.6000]
    'Country2'    'Name14'    [0.4000]
    'Country3'    'Name21'    [0.5000]
    'Country4'    'Name31'    [0.4000]
    'Country4'    'Name25'    [0.6000]
    'Country2'    'Name14'    [0.4000]
    'Country4'    'Name40'    [0.5000]
    'Country4'    'Name31'    [0.4000]
    'Country4'    'Name25'    [0.6000]

[Mekala balaji]

I used the below code: clc; clear all; close all; [num ,txt,CNL]=xlsread('RecipeandHTDList.xlsx'); [num2,txt2,NDL]=xlsread('RecipeList.xlsx');

X = cellfun(@(c)find(strcmp(c,CNL(:,2))),NDL(:,1)); out = [CNL(X,1),NDL]; but I got the error:

??? Error using ==> cellfun Non-scalar in Uniform output, at index 7, output 1. Set 'UniformOutput' to false.

Error in ==> Matchingrearrangingdata at 7 X = cellfun(@(c)find(strcmp(c,CNL(:,2))),NDL(:,1));

[Stephen23]

Yes, I was wondering if that would happen. That error means that for one (or more) of the NDL strings either a) no match can be found, or b) multiple matches have been found. Either of these will cause this error. You could try using strcmpi instead of strcmp if you think that case differences are causing the error.

Otherwise please upload the two data files (or even better, just NDL and CNL in a .MAT file), and I will look at what the problem is.

You also need to decide how you want to deal with non-matching / multi-matching entries.

[Mekala balaji]

Sir, I have uploaded the files, kindly help me. Many many thanks.

[Stephen23]

The filenames that you uploaded do not match the filenames in your code.

However the problem is rather obvious: many of the names look identical, but actually most of them contain (a seemingly random number of) trailing space characters. If you remove these space characters, then my code works perfectly. I removed these trailing space characters from all strings using strtrim :

CNL = strtrim(CNL);
NDL(:,1) strtrim(NDL(:,1));

But a better solution would be to not use excel spreadsheets to store your data. Using spreadsheets makes it easy to hide such mistakes, and the risk of carrying viruses makes them unattractive to lots of people you might like to work with. I recommend that you store your data in CSV files rather than excel workbooks: they would be a little bit easier to work with, make space characters much more obvious, and do not have the risk of carrying viruses.