title: Data Manipulation / Analysis / Aggregation using EXCEL instead of R or SQL -- Composite Key of 2 columns, calculated sum of counts
body: <p>I asked around and still no solution.  People just told me "easy with PivotTable", but said easier than done, and no solution so far.  </p>
  
  <p>I would like to use Excel 2013/2016 to do the following:</p>
  
  <p><strong>1. Raw Data</strong></p>
  
  <p>I have a <strong>comma separated</strong> text file like this, with 2.8 million rows:</p>
  
  <p><strong>Table 1:</strong></p>
  
  <pre><code>Cell_Id, Gene_Id, Count
  gcatatgc,AAK1,36
  gcatatgc,ABHD14B,5
  gcatatgc,ABHD17B,20
  tttggcca,ABI3,4
  tttggcca,AAK1,50
  tttggcca,ABHD14B,6
  aaaggctt,ABT1,5
  aaaggctt,ACAA1,14
  aaaggctt,AAK1,50
  aaaggctt,ABHD14B,6
  ggaggcaa,AAK1,50
  ggaggcaa,ABHD14B,6
  ggaggcaa,AAK1,36
  ggaggcaa,ABHD14B,5
  ......
  ....
  </code></pre>
  
  <p>The <code>Cell_ID</code> and <code>Gene_ID</code> have duplicates.   </p>
  
  <p><strong>2. Step A</strong>. from above CSV text file, I want to create a table, of distinct <code>Cell_ID</code> and generate new column with unique key number of each distinct <code>Cell_ID</code>.  </p>
  
  <p><strong>generated Table 2:</strong></p>
  
  <pre><code>Distinct_Cell_ID, Key_Distinct_Cell_ID
  gcatatgc, 1
  tttggcca, 2
  aaaggctt, 3 
  ggaggcaa, 4
  ......
  ....
  </code></pre>
  
  <p><strong>3. Step B</strong>, also create another table, of distinct Gene_ID and generate new column with unique key number of each distinct Gene_ID.</p>
  
  <p><strong>generated Table 3:</strong></p>
  
  <pre><code>Distinct_Gene_ID, Key_Distinct_Gene_ID
  AAK1,1
  ABHD14B,2
  ABHD17B,3
  ABI3,4
  ABT1,5
  ACAA1,6
  ......
  ....
  </code></pre>
  
  <p><strong>4. Step C</strong>, generate a new table, from Table 1, 2 and 3, group by <code>Distinct_Cell_ID</code> and <code>Distinct_Gene_ID</code>, and generate a group count sum, grouped by <code>(Distinct_Cell_ID</code>, <code>Distinct_Gene_ID</code>) unique pairs.</p>
  
  <p><strong>generated Table 4:</strong></p>
  
  <pre><code>Key_Distinct_Cell_ID, Key_Distinct_Gene_ID, Sum_Group_Count
  1, 1, 36
  1, 2, 5
  1, 3, 20
  2, 4, 4
  2, 1, 50
  2, 3, 6
  3, 5, 50
  3, 6, 6
  3, 1, 36
  3, 2, 5
  ......
  ....
  </code></pre>
  
  <p>and output this Table 4 to a Comma Separated CSV file.</p>
  
  <p><strong>5. Step D</strong>, generate 3 "Total Values"</p>
  
  <pre><code>Total count of Distinct_Cell_ID's, 
  Total count of Distinct_Gene_ID's, 
  Total sum of count in Table 1.
  </code></pre>
  
  <p>I would appreciate if someone can help, to show me how to do steps A, B, C and D in EXCEL 2013 or EXCEL 2016.   </p>
  
  <ul>
  <li><p>need to extract "distinct" values (remove duplicates) from one column to another column, and then generate a serial-key number next to new column, for both column Cell_ID (new columns Distinct_Cell_ID and Key_Distinct_Cell_ID) and column Gene_ID (new columns Distinct_Gene_ID and Key_Gene_Cell_ID).</p></li>
  <li><p>Create another table with columns Key_Distinct_Gene_ID, Key_Distinct_Cell_ID, and generate a 3rd column of the sum of "Count"s, looking up raw data table 1, with combined-key of (Distinct_Gene_ID,  Distinct_Cell_ID) == (Gene_ID,  Cell_ID)</p></li>
  <li><p>Also the table 1 to 4 and the values in step D, <strong>can be refreshed</strong> when the source CSV file changes, perhaps by one click of an EXCEL feature button.</p></li>
  </ul>
  
  <p>(Although it look like easier to do it by R/Python program or SQL Server, but I wonder how to do this in Excel). </p>
  
  <p>I have written a R Markdown script to this this, but still cannot figure out how to do it in EXCEL.</p>
  
  <p>Said is easier than done.</p>
  
  <p>I hope this question is specific enough for someone to answer.</p>
  
  <p>Regards
  Alex</p>