PowerShell script to compare a list of names in one excel column to a list of names in another column
In my previous three posts, I described the technique to open excel workbook/worksheet and then get our focus on the working range. So we can use the ranges to do some work.
Here is a PS script to compare a list of names in one excel column X to a list of names in another column Y. If I find the column X name in column Y, I will write a simple yes or no in the column next to column X and color it differently.
#Create excel object and make the object visible
$excel = New-Object -comobject Excel.Application
$excel.visible = $True
# Use Inputbox to get the full path of the target file and create a $workbook object to hold it
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the full path of your excel file`",`"Workbook Name`") end function" )
$WorkbookFullPath = $x.eval("getInput")
$workbook = $excel.Workbooks.Open($WorkbookFullPath)
# Use Inputbox to get the target worksheet in the target file and create a $worksheet object to hold it
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the worksheet name`",`"WorkSheet Name`") end function" )
$worksheetname = $x.eval("getInput")
$worksheet = $workbook.Worksheets.Item($WorksheetName)
#Create $SearchRange and $CompareRange objects to hold the ranges – the ranges already defined in target file
$SearchRange = $worksheet.Range("SearchSource")
$Comparerange = $worksheet.Range("SearchTarget")
#Loop through $SearchRange and determine if the names can be found in $Comparerange - start from row 2 because the first row contain headers.
for($i = 2; $i -le $SearchRange.count; $i++)
{
$strComputer = $worksheet.cells.item($i,3).Text
$search = $Comparerange.find($strcomputer)
if($search -eq $null)
{
$worksheet.cells.Item($i,4).Interior.ColorIndex = 3
$worksheet.cells.Item($i,4) = "No"
}
Else
{
$worksheet.cells.Item($i,4).Interior.ColorIndex = 4
$worksheet.cells.Item($i,4) = "Yes"
}
}
$i = $i + 1
# Save the results and gracefuly shutdown excel.
$excel.save()
$a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet)
$a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
$a = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)