Compare-Object returns only one match of many

I have 2 spreadsheets that I have imported to PowerShell arrays.

They BOTH have USERNAME as a property, but otherwise they have different properties.

In one of them (array1) the USERNAME is unique among 300 entries; in the other (array2), a given USERNAME may have multiple entries among 30,000 entries, & not all of the USERNAMEs will appear in both.

Cycling thru the smaller array1 picking out the matches in array2 takes hours to complete:

foreach ($item in $array1)
    $matches += @($array2 | where {$_.USERNAME -eq $($item.USERNAME)})

However, using Compare-Object is much quicker (a matter of a few seconds) BUT only gives the 1st match from array2:

$matches = Compare-Object -ReferenceObject $array2 -DifferenceObject $array1 -Property USERNAME -ExcludeDifferent -IncludeEqual -PassThru

…so I lose the multiple matches from array2; if I remove the -ExcludeDifferent switch then the SideIndicator results for multiple matches show the 1st of the multiple matches “==” but the subsequent multiple matches as “<=”.

Is there a way to get Compare-Object to correctly return all the array2 matches? Or is there a better way?

Thanks in advance for your replies! :slight_smile:


If I got you right this should speed up the task a little bit:

$result = foreach ($item in $array2) {
    $item | Where-Object {
        $item.Username -in $array1.Username
$result | Sort-Object -Property username

This should need only one run through the $array2.