title: How to do a many to many merge of two CSV files with Powershell?
body: <p>I've got two different CSVs. One is a report of global groups, local groups and systems. The other is a list of users, logon IDs, and global groups.</p>
  
  <p>As you can see, Global AD Groups is the common column and is labeled "Members" in both files. Some of the items in Members are actually either local users or domain users. Local users need to be filtered out and domain users need to be ported to the results.</p>
  
  <p>What makes this task difficult is that the join is a many to many. Also, I need to be able to easily add new columns to the script. I already know how to take the output of whatever script you create and reorder the columns as needed.</p>
  
  <p>Ideally, it shouldn't matter what columns are in each CSV file and the script should join all columns from both files together based on the common column. Although, it would be nice if the common column could be specified when the function is called.</p>
  
  <p>So sample data would look like:</p>
  
  <pre><code>$CSV1
  "Members","LocalGroup","System"
  "System1\local-user","Guests","System1"
  "adminaccounts","Administrators",System2"
  "adminaccounts","Auditors","System2"
  "System3\User1","Administrators","System3"
  "System3\Temp","Guests","System3"
  "System4\Temp","Guests","System4"
  "System5\Temp","Guests","System5"
  "Domain1\ngd","Users","System6"
  "Domain1\Group1","Administrators","System7"
  "Domain1\Group1","Administrators","System8"
  "Domain1\Group1","Administrators","System9"
  "Domain1\Group1","Administrators","System10"
  "Domain1\Group2","Administrators","System1"
  "Domain1\Group2","Administrators","System4"
  "Domain1\Group2","Administrators","System7"
  "Domain1\Group3","Administrators","System3"
  "Domain1\Group3","Administrators","System1"
  "Domain1\Group4","Administrators","System4"
  "Domain2\Group1","Administrators","System5"
  "Domain2\Group1","Administrators","System6"
  "Domain2\Group1","Administrators","System2"
  
  $CSV2
  "Members","Username","Logon ID"
  "Domain1\Group1","Pauline S. Trotter","pst"
  "Domain1\Group1","Caroline T. Gartner","ctg"
  "Domain1\Group1","Albert F. Hollis","afh"
  "Domain1\Group1","Todd C. Cope","tcc"
  "Domain1\Group1","Rebecca D. Bergman","rdb"
  "Domain1\Group1","Robert A. Wagner","raw"
  "Domain1\Group2","Daniel R. Dalton","drd"
  "Domain1\Group2","Christopher V. Alderbrook","cva"
  "Domain1\Group2","Brittany L. Stone","bls"
  "Domain1\Group2","Susan K. Holman","skh"
  "Domain1\Group2","Mark P. Bett","mpb"
  "Domain1\Group2","Francine U. Zen","fuz"
  "Domain1\Group2","Holly M. Fulton","hmf"
  "Domain1\Group3","Edgar X. Indiana","exi"
  "Domain1\Group3","John Q. Pullit","jqp"
  "Domain1\Group3","Victor E. Emmerson","vee"
  "Domain1\Group3","Robert A. Wagner","raw"
  "Domain1\Group3","Pauline S. Trotter","pst"
  "Domain1\Group4","Noreen G. Dulles","ngd"
  "Domain1\Group4","Lois H. Crantz","lhc"
  "Domain1\Group4","Brad F. Quilt","bfq"
  "Domain1\Group4","Zoren T. Maranda","ztm"
  "Domain1\Group4","William K. Entz","wke"
  "Domain1\Group4","Quils U. Naruz","qun"
  "Domain2\Group1","Zoren T. Maranda","ztm"
  "Domain2\Group1","Robert A. Wagner","raw"
  "Domain2\Group1","Holly M. Fulton","hmf"
  "Domain2\Group1","Brad F. Quilt","bfq"
  "Domain2\Group1","Pauline S. Trotter","pst"
  "Domain2\Group1","Noreen G. Dulles","ngd"
  </code></pre>
  
  <p>Thanks in advance for your help.</p>
  
  <p>Regards,</p>
  
  <p>Alex</p>
  
  <p>Here's some simplified data with sample output:</p>
  
  <pre><code>$CSV1 
  "Members","LocalGroup","System" 
  "System1\local-user","Guests","System1" 
  "adminaccounts","Administrators",System2" 
  "adminaccounts","Auditors","System2" 
  "System3\User1","Administrators","System3" 
  "System3\Temp","Guests","System3" 
  "System4\Temp","Guests","System4" 
  "System5\Temp","Guests","System5" 
  "Domain1\ngd","Users","System6" 
  "Domain1\Group1","Administrators","System7" 
  "Domain1\Group1","Administrators","System8" 
  "Domain1\Group2","Administrators","System1" 
  "Domain1\Group3","Administrators","System3" 
  "Domain1\Group3","Administrators","System1" 
  "Domain1\Group4","Administrators","System4" 
  "Domain2\Group1","Administrators","System5" 
  
  $CSV2 
  "Members","Username","Logon ID" 
  "Domain1\Group1","Pauline S. Trotter","pst" 
  "Domain1\Group1","Caroline T. Gartner","ctg" 
  "Domain1\Group1","Albert F. Hollis","afh" 
  "Domain1\Group2","Daniel R. Dalton","drd" 
  "Domain1\Group2","Christopher V. Alderbrook","cva" 
  "Domain1\Group3","Pauline S. Trotter","pst" 
  "Domain1\Group4","Noreen G. Dulles","ngd" 
  "Domain1\Group4","Quils U. Naruz","qun" 
  "Domain2\Group1","Zoren T. Maranda","ztm" 
  "Domain2\Group1","Noreen G. Dulles","ngd" 
  
  Output 
  "Username","Logon ID","Members","LocalGroup","System" 
  ,,"System1\local-user","Guests","System1" 
  ,,"adminaccounts","Administrators",System2" 
  ,,"adminaccounts","Auditors","System2" 
  ,,"System3\User1","Administrators","System3" 
  ,,"System3\Temp","Guests","System3" 
  ,,"System4\Temp","Guests","System4" 
  ,,"System5\Temp","Guests","System5" 
  "Pauline S. Trotter","pst","Domain1\Group1","Administrators","System7" 
  "Pauline S. Trotter","pst","Domain1\Group1","Administrators","System8" 
  "Pauline S. Trotter","pst","Domain1\Group3","Administrators","System3" 
  "Pauline S. Trotter","pst","Domain1\Group3","Administrators","System1" 
  "Caroline T. Gartner","ctg","Domain1\Group1","Administrators","System7" 
  "Caroline T. Gartner","ctg","Domain1\Group1","Administrators","System8" 
  "Albert F. Hollis","afh","Domain1\Group1","Administrators","System7" 
  "Albert F. Hollis","afh","Domain1\Group1","Administrators","System8" 
  "Daniel R. Dalton","drd","Domain1\Group2","Administrators","System1" 
  "Christopher V. Alderbrook","cva","Domain1\Group2","Administrators","System1" 
  "Noreen G. Dulles","ngd","Domain1\Group4","Administrators","System4" 
  "Noreen G. Dulles","ngd","Domain2\Group1","Administrators","System5" 
  "Quils U. Naruz","qun","Domain1\Group4","Administrators","System4" 
  "Zoren T. Maranda","ztm","Domain2\Group1","Administrators","System5" 
  </code></pre>