title: How to connect two variables by an overlapping name in excel or linux
body: <p>I first tried to solve it myself with excel and later in Linux, but I cannot seem to solve it. So hope someone can help me with solving this! And not really good in explaining things, so if anything is unclear, please let me know.</p>
  
  <p>I have a list of around 30,000 genes starting with <code>Bol</code> and their orthologous genes starting with <code>At</code>, meaning the <code>Bol</code> genes are of the same function as <code>At</code>. I know the <code>GO</code> terms (function terms of genes) for the <code>At</code> genes, but not for the Bol genes. But as I also have a list connecting the <code>At</code> genes to the <code>Bol</code>, I can connect the <code>GO</code> terms to the <code>Bol</code> genes with the help of the overlapping <code>At</code> genes. Once that list is finished I want to count how many of each <code>GO</code> term I have in the total <code>Bol</code> gene set. </p>
  
  <p>The files I have look like these:</p>
  
  <p>Small example of sheet 1</p>
  
  
  
  <pre class="lang-none prettyprint-override"><code>Bol040727   AT1G01010
  Bol040726   AT1G01020
  Bol040729   AT1G01020
  Bol040725   AT1G01030
  Bol040724   AT1G01040
  Bol040723   AT1G01050
  Bol018442   AT1G01050
  Bol040721   AT1G01070
  Bol018443   AT1G01070
  Bol040718   AT1G01090
  Bol018445   AT1G01090
  Bol040717   AT1G01100
  Bol018446   AT1G01100
  Bol040716   AT1G01110
  Bol040715   AT1G01120
  Bol018447   AT1G01120
  Bol000520   AT1G01120
  Bol000521   AT1G01120
  Bol040714   AT1G01140
  Bol040713   AT1G01160
  Bol018449   AT1G01160
  Bol040712   AT1G01170
  Bol040711   AT1G01180
  Bol040710   AT1G01190
  Bol018450   AT1G01190
  Bol040709   AT1G01200
  Bol018452   AT1G01210
  Bol018453   AT1G01220
  Bol018454   AT1G01225
  Bol040708   AT1G01230
  Bol018455   AT1G01230
  Bol040707   AT1G01240
  Bol018456   AT1G01240
  Bol040706   AT1G01250
  Bol018457   AT1G01250
  </code></pre>
  
  <p>Small example of sheet 2<br/></p>
  
  <pre class="lang-none prettyprint-override"><code>AT1G01010   GO:0045449  GO:0005575  GO:0003700  GO:0007275          
  AT1G01020   GO:0005783  GO:0016020  GO:0003674  GO:0006665  GO:0016125      
  AT1G01030   GO:0005575  GO:0003700  GO:0009908  GO:0045449  GO:0048366      
  AT1G01040   GO:0005634  GO:0008026  GO:0003725  GO:0005515  GO:0004525  GO:0006396
  AT1G01046   GO:0005575  GO:0003674  GO:0008150              
  AT1G01050   GO:0006796  GO:0005737  GO:0016020  GO:0005634  GO:0004427  GO:0008152  
  AT1G01060   GO:0003677  GO:0003700  GO:0009651  GO:0009737  GO:0009733  GO:0009723
  AT1G01070   GO:0016020                      
  AT1G01073   GO:0012505                      
  AT1G01073   GO:0003674  GO:0008150                  
  AT1G01080   GO:0009507  GO:0009570  GO:0005634  GO:0003676  GO:0000166  GO:0009941
  AT1G01090   GO:0009507  GO:0006096  GO:0008152  GO:0009941  GO:0055114  GO:0009536
  AT1G01100   GO:0005886  GO:0003735  GO:0006414  GO:0022626  GO:0005829  GO:0005634
  AT1G01110   GO:0005739  GO:0003674                  
  AT1G01115   GO:0012505                      
  AT1G01115   GO:0003674  GO:0008150                  
  AT1G01120   GO:0022626  GO:0005783  GO:0016020  GO:0008415  GO:0009409  GO:0009416
  AT1G01130   GO:0005575  GO:0003674  GO:0008150
  </code></pre>
  
  <p>So <code>AT1G01073</code> and <code>AT1G01115</code> are in duplicate, It should be:</p>
  
  <pre class="lang-none prettyprint-override"><code>AT1G01073 GO:0012505 GO:0003674 GO:0008150
  AT1G01115 GO:0012505 GO:0003674 GO:0008150 
  </code></pre>
  
  <p>And as output I would like for instance to get this</p>
  
  <pre class="lang-none prettyprint-override"><code>Bol040727 AT1G01010 GO:0045449  GO:0005575  GO:0003700  GO:0007275
  Bol040726 AT1G01020 GO:0005783  GO:0016020  GO:0003674  GO:0006665  GO:0016125
  </code></pre>
  
  <p>etc.</p>
  
  <p>I tried to connect sheet 1 with sheet 2 with <code>VLOOKUP</code> in excel, but it seems that some genes in sheet 2 are in duplicate but with different <code>GO</code> terms. So I thought about finding the duplicated genes and paste the <code>GO</code> terms together to create a list of only unique At genes with <code>GO</code> terms. Finding the duplicated At genes is no problem, but how to paste the <code>GO</code> terms together in an easy and quick way (there are a lot of duplicated At genes). And counting the <code>GO</code> terms I can easily do if everything is in one column, but here it is not.</p>
  
  <p>So is there anyone who knows how to solve this or who knows another maybe easier solution? C++ code in Linux or a simple perl/ python script is also fine by me. The columns in the files are tab delimited.</p>
  
  <p>Thanks a lot!</p>