Comparing values between arrays is very slow

Question

I am a novice Powershell user and would like help with the following:

I am comparing the values in one one array with that of another. If they match, I write the value to a cell, if there is no match, the cell is highlighted red. However, with only two small arrays (each ~200 values) the search takes hours. There must be better way, please help.

$ArrFinal = $arrA + $arrB + $arrC + $arrD
$ArrFinal = $ArrFinal | select -uniq | sort-object
for ($k=1; $k -lt $ArrFinal.length; $k++)
{
for ($j=1; $j -lt $arrA.length; $j++)
    {
    if($ArrFinal[$k] -like $arrA[$j])
            {
                $cells.item($k+1,2)=$arrA[$j]
                $cells.item($k+1,2).Interior.ColorIndex = 2
                break
            }
    else
            {
                $cells.item($k+1,2).Interior.ColorIndex = 3
            }
      }
  }

score 3 · Answer 1 · edited May 23 '17 at 12:28

Assuming you're talking about Excel here: don't color each cell separately. Set ColorIndex to 3 once for the entire range and only change a cell's color when you actually change its value. Better yet, use a conditional format that will color empty cells differently from non-empty cells.

Also I'd drop the inner loop. You want to check if the 2^nd array contains the value from the 1^st one, so you can just use the -contains operator and write the value from the 1^st array to the cell ($ArrFinal[$k] and $arrA[$j] are equal after all).

$ArrFinal = $arrA + $arrB + $arrC + $arrD | select -uniq | sort-object

for ($k=1; $k -lt $ArrFinal.length; $k++) {
  if ($arrA -contains $ArrFinal[$k]) {
    $cells.Item($k+1, 2) = $ArrFinal[$k]
    $cells.Item($k+1, 2).Interior.ColorIndex = 2
  }
}

Comparing values between arrays is very slow

1 Answers1