title: Partial id match and merge multiple to one
body: <p>I have two files, File1 and File2. File1 has 6000 rows and file2 has 3000 rows. I want to match the ids and merge the files based on matches, which is simple. But, the ids in file1 and file2 only match partially. Have a look at the files. For every id (row) in file2 there must be two matching ids (rows) in file 1. Also, not all the ids in file2 are present in file1. I had tried awk but didn't get the desired output.</p>
  
  <p>File1</p>
  
  <pre><code>1_A01_A
  1_A01_B
  2_B03_A
  2_B03_B
  1_A02_A
  1_A02_B
  2_B04_A
  2_B04_B
  1_A03_A
  1_A03_B
  2_B05_A
  2_B05_B
  1_A04_A
  1_A04_B
  2_B06_A
  2_B06_B
  1_A06_A
  1_A06_B
  2_B07_A
  2_B07_B
  1_A07_A
  1_A07_B
  2_B08_A
  2_B08_B
  9_F10_A
  9_F10_B
  12_D08_A
  12_D08_B
  5505744243493_F09.CEL_A_A
  5505744243493_F09.CEL_B_B
  </code></pre>
  
  <p>File2</p>
  
  <pre><code>1_A01   14
  2_B03   13
  1_A02   4
  2_B04   14
  1_A03   11
  2_B05   8
  1_A04   18
  2_B06   15
  1_A06   10
  2_B07   4
  1_A07   8
  2_B08   22
  1_A08   5
  2_B09   15
  1_A09   20
  2_B10   17
  </code></pre>