Merge two Excel spread sheets using a common column and retaining duplicates

Question

There are two excel sheets. I am trying to merge the two files based on the id column. For example: first excel:

id var1 var2 var3            
1     5    4    5             
2     3    2    2             
3     1    4    7              
1     4    6    7             

second excel:

id var4 var5 var6        
1    2    3    5    
1    9    0    1    
2    8    6    4   
2    5    2    1   
3    0    3    1   
3    2    1    4   

Would result in:

id var4 var5 var6 var1 var2 var3   
1     2    3    5    5    4    5      
1     9    0    1    4    6    7    
2     8    6    4    3    2    2   
2     5    2    1   
3     0    3    1    1    4    7   
3     2    1    4   

Notice how if the tables have the same number of rows of data, it will include it, otherwise it will be left blank.

I have tried to use the vlookup function and consolidate, but it doesn't seem to exactly do this.

I am relatively new to Excel. Any help would be greatly appreciated.

Answer 1

I would create a new ID which is unique, and incorporates the order of the appearance of the IDs. One way to do that would if the first excel starts in A1, so A1 = 'id', then enter in E2:

=A2+COUNTIF($A$2:A2,A2)/10000

Choose the divisor (10000 in this case) so that it is larger than the expected number of duplicates.

Do the same for both tables, and then you can use these unique IDs to join the tables using VLOOKUP, INDEX/MATCH, etc.

IFERROR can be used to return "" instead of #N/A for missing rows.