title: Compare and merge 2 csv files based on 2 first columns with possible duplicate values
body: <p>I have 2 csv files I'm asked to merge where the values from the first column match. Both files can have the possibility of having duplicate values, and if they do, a new row should be created to support those values. If no match is found, then print the value no match.</p>
  
  <p>Except for looking for duplicate values, I am using the following code...</p>
  
  <pre><code>Function GetFirstColumnNameFromFile
  {
      Param ($CsvFileWithPath)
  
      $FirstFileFirstColumnTitle = ((Get-Content $CsvFileWithPath -TotalCount 2 | ConvertFrom-Csv).psobject.properties | ForEach-Object {$_.name})[0]
      Write-Output $FirstFileFirstColumnTitle
  }
  
  Function CreateMergedFileWithCsv2ColumnOneColumn
  {
      Param ($firstColumnFirstFile, $FirstFileFirstColumnTitle, $firstFile, $secondFile, $resultsFile)
  
      Write-Host "Creating hash table with columns values `"Csv2ColumnOne`" `"Csv2ColumnTwo`" From $secondFile"
      $hashColumnOneColumnTwo2ndFile = @{}
      Import-Csv $secondFile | Where-Object {$firstColumnFirstFile -contains $_.'Csv2ColumnOne'} | ForEach-Object {$hashColumnOneColumnTwo2ndFile[$_.'Csv2ColumnOne'] = $_.Csv2ColumnTwo}
      Write-Host "Complete."
  
      Write-Host "Creating Merge file with file $firstFile
      and column `"Csv2ColumnTwo`" from file $secondFile"
      Import-Csv $firstFile | Select-Object *, @{n='Csv2ColumnOne'; e={
      if ($hashColumnOneColumnTwo2ndFile.ContainsKey($_.$FirstFileFirstColumnTitle)) {
          $hashColumnOneColumnTwo2ndFile[$_.$FirstFileFirstColumnTitle]
      } Else {
          'Not Found'
      }}} | Export-Csv $resultsFile -NoType -Force
      Write-Host "Complete."
  }
  
  Function MatchFirstTwoColumnsTwoFilesAndCombineOtherColumnsOneFile
  {
      Param ($firstFile, $secondFile, $resultsFile)
  
      [string]$FirstFileFirstColumnTitle = GetFirstColumnNameFromFile $firstFile
  
      $FirstFileFirstColumn = Import-Csv $firstFile | Where-Object {$_.$FirstFileFirstColumnTitle} | Select-Object -ExpandProperty $FirstFileFirstColumnTitle
  
      CreateMergedFileWithCsv2ColumnOneColumn $FirstFileFirstColumn $FirstFileFirstColumnTitle $firstFile $secondFile $resultsFile
  }
  
  Function Main
  {
      $firstFile = 'C:\Scripts\Tests\test1.csv'
      $secondFile = 'C:\Scripts\Tests\test2.csv'
      $resultsFile = 'C:\Scripts\Tests\testResults.csv'
  
      MatchFirstTwoColumnsTwoFilesAndCombineOtherColumnsOneFile $firstFile $secondFile $resultsFile
  }
  
  Main
  </code></pre>
  
  <p>The contents of the first csv file is:</p>
  
  <pre><code>firstName,secondName
  1234,Value1
  2345,Value1
  3456,Value1
  4567,Value1
  7645,Value3
  </code></pre>
  
  <p>The contents of the second csv file is:</p>
  
  <pre><code>Csv2ColumnOne,Csv2ColumnTwo,Csv2ColumnThree
  1234,abc,Value1
  1234,asd,Value1
  3456,qwe,Value1
  4567,mnb,Value1
  </code></pre>
  
  <p>The results is:</p>
  
  <pre><code>"firstName","secondName","Csv2ColumnOne"
  "1234","Value1","asd"
  "2345","Value1","Not Found"
  "3456","Value1","qwe"
  "4567","Value1","mnb"
  "7645","Value3","Not Found"
  </code></pre>
  
  <p>Since the second file has a duplicate value of <code>1234</code> the result file should be:</p>
  
  <pre><code>"firstName","secondName","Csv2ColumnOne"
  "1234","Value1","abc"
  "1234","Value1","asd"
  "2345","Value1","Not Found"
  "3456","Value1","qwe"
  "4567","Value1","mnb"
  "7645","Value3","Not Found"
  </code></pre>
  
  <p>Is there a way I can do this?</p>