title: Transform Excel Table Using Column Value as Category
body: <p>I have an Excel table with data structured similar to:</p>
  
  <pre><code>Name                 Team        Role        Level   Laptop Monitor
  Cheryle Parish       Archers     Programmer  Level2  Dell   2
  Shandi Kime          Archers     Programmer  Level2  Dell   1
  Edwardo Cookson      Archers     Programmer  Level1  HP     3
  Voncile Deak         Archers     Programmer  Level2  HP     3
  Boyd Cueva           Climbers    Tester      Level2  HP     2
  Caryn Kear           Climbers    Programmer  Level2  Apple  1
  Kirsten Hirt         Climbers    Tester      Level2  Apple  3
  Ayana Mayfield       Climbers    Programmer  Level2  Dell   2
  Venetta Embree       Archers     Programmer  Level2  Dell   3
  Herb Obryan          Archers     Programmer  Level2  Dell   2
  Isobel Steinmetz     Explorers   Tester      Level2  Apple  3
  Valeri Eisen         Explorers   Programmer  Level1  Dell   1
  Anibal Plumlee       Explorers   Programmer  Level1  Dell   3
  Floretta Tunney      Explorers   Tester      Level1  Apple  1
  Lilliana Klutts      Explorers   Programmer  Level1  HP     2
  Gaston Custodio      Explorers   Programmer  Level1  HP     2
  Jared  Phaneuf       Explorers   Manager     Level1  Dell   3
  Melvin Monsivais     Climbers    Manager     Level2  HP     2
  </code></pre>
  
  <p>Is there any simple way to create a new table or tables where one of the columns represents the categories and the values in that column are the values in another column? For example, the above list if I chose "Role" the output table(s) are:</p>
  
  <pre><code>Programmer           Manager              Tester
  Cheryle Parish       Jared  Phaneuf       Boyd Cueva           
  Shandi Kime          Melvin Monsivais     Floretta Tunney      
  Edwardo Cookson                           Isobel Steinmetz     
  Voncile Deak                              Kirsten Hirt         
  Caryn Kear
  Ayana Mayfield
  Venetta Embree       
  Herb Obryan          
  Valeri Eisen         
  Anibal Plumlee       
  Lilliana Klutts      
  Gaston Custodio      
  </code></pre>
  
  <p>The other entries in the source table are thrown away in the new tables. Ideally if there are any duplicates, the duplicates are ignored and the new tables only have unique values. A VBA solution would work, trying to avoid an external program.</p>
  
  <p>We could choose any of the columns to be the category for classification in the new tables. For example, we could generate a lists of whoever has a particular laptop brand.</p>
  
  <p>The closest I can come up with is to use pivot tables to generate something that would still need copy-and-paste type editing. </p>
  
  <pre><code>Manager     Jared  Phaneuf
              Melvin Monsivais
  Programmer  Anibal Plumlee
              Ayana Mayfield
              Caryn Kear
              Cheryle Parish
              Edwardo Cookson
              Gaston Custodio
              Herb Obryan
              Lilliana Klutts
              Shandi Kime
              Valeri Eisen
              Venetta Embree
              Voncile Deak
  Tester      Boyd Cueva
              Floretta Tunney
              Isobel Steinmetz
              Kirsten Hirt
  </code></pre>
  
  <p>I found this question which is similar <a href="https://stackoverflow.com/questions/49745449/excel-categorize-table-by-column">Excel Categorized table by column</a>, but the answer doesn't solve the above problem.</p>