Combining excel files with unequal rows

I have two excel files which I would like to combine into a single file. The data is as follows

:

I would like to combine these two tables into a single table with the columns "Name" "Date" "Value 1" "Value2" where the occassion of John 04-05-2011 shows a value for Value 1 but remains empty for Value 2.

Answer 1

[Dyuman Joshi]

Try outerjoin -

%Data from the images attached for example
%Use readtable() to read the data as tables directly
Name = ["Adam" "Adam" "John" "John" "John" "Karen"].';
Date = datetime(2010, [11 12 11 12 5 5], [5 6 5 6 4 4]).';
Value1 = [12 16 8 3 14 12].';

t1 = table(Name, Date, Value1)

t1 = 6x3 table
     Name         Date        Value1
    _______    ___________    ______

    "Adam"     05-Nov-2010      12  
    "Adam"     06-Dec-2010      16  
    "John"     05-Nov-2010       8  
    "John"     06-Dec-2010       3  
    "John"     04-May-2010      14  
    "Karen"    04-May-2010      12  

Name = ["Adam" "Adam" "John" "John" "Karen"].';
Date = datetime(2010, [11 12 11 12 5], [5 6 5 6 4]).';
Value2 = [8 6 1 3 8].';

t2 = table(Name, Date, Value2)

t2 = 5x3 table
     Name         Date        Value2
    _______    ___________    ______

    "Adam"     05-Nov-2010      8   
    "Adam"     06-Dec-2010      6   
    "John"     05-Nov-2010      1   
    "John"     06-Dec-2010      3   
    "Karen"    04-May-2010      8   

%join tables
out = outerjoin(t1, t2, 'Keys', [1 2])

out = 6x6 table
    Name_t1      Date_t1      Value1     Name_t2       Date_t2      Value2
    _______    ___________    ______    _________    ___________    ______

    "Adam"     05-Nov-2010      12      "Adam"       05-Nov-2010       8  
    "Adam"     06-Dec-2010      16      "Adam"       06-Dec-2010       6  
    "John"     04-May-2010      14      <missing>            NaT     NaN  
    "John"     05-Nov-2010       8      "John"       05-Nov-2010       1  
    "John"     06-Dec-2010       3      "John"       06-Dec-2010       3  
    "Karen"    04-May-2010      12      "Karen"      04-May-2010       8  

[Katelyn]

Thak you for your response! The final format that you have provided would work fine for the final output. I am wondering if there is a way to acheive this final output without manually entering the data vectors into the code, the data that I have provided is just a snippet of more than 3000 entires.

[Dyuman Joshi]

Yes, in that case read the data from the files using readtable and call outerjoin() afterwards.

[Katelyn]

Thank you so much! I tried this and it worked perfectly.

[Dyuman Joshi]

Glad to have helped!