Use powershell to compare columns in excel

Article
2019-10-01

Question

_{Tuesday, October 1, 2019 7:29 AM}

I have an excel workbook with two sheets. I want to compare two columns in different sheets using powershell.

Say column C in Sheet 1 needs to be compared to column D in Sheet2. Column C has say 100 cells and Column D has 120 cells.

I want to compare each cell value in Column C with all cells values in ColumnD and vice versa. So far I have something like this, however it does meet the above requirement:

Param(
$path = "C:\Users\Documents\Book22.xlsx",
$worksheet1 = “1”,
$worksheet2="2"
)
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($path)
$ws1 = $Workbook.WorkSheets.item($worksheet1)
$ws2 = $Workbook.WorkSheets.item($worksheet2)
$ws2.activate()
$intRow=2
$intCol1=5
$inCol2=6
Do{
if($ws2.Cells.Item($intRow,$intCol2).Value() -eq $ws1.Cells.Item($intRow,$intCol1).Value()){
$ws2.Range("($intRow):$($intRow)").interior.colorindex=3
}
else{
$ws2.Range("($intRow):$($intRow)").interior.colorindex=5
}
$intRow++
}While ($objNewWorksheet.Cells.Item($intRow,$intCol).Value() -ne $null)
$workbook.save()
$excel.Quit()

All replies (7)

_{Tuesday, October 1, 2019 7:51 AM ✅Answered}

Please post your code correctly us9ing the tool provided.

How to post code in Technet Forums

Always start simple. Simplifying will make understanding easier. Get fancy only after you can see how it works.

$path = 'C:\Users\Documents\Book22.xlsx'

$xl = New-Object -ComObject excel.application
$xl.visible = $false
$wb = $xl.Workbooks.open($path)
$ws1 = $wb.WorkSheets[1]
$ws2 = $wb.WorkSheets[2]

$maxrows = $ws1.UsedRange.Rows.Count
for($row = 2;$row -lt $maxrows;$row++){
    if($ws2.Rows[$row].Cells[6).Value2 -eq $ws1.Rows[$row].Cells[5].Value2){
        # cells match
    }else{
        # cells do not match
    }
}

\(ツ)_/

_{Tuesday, October 1, 2019 9:53 AM}

Thanks jrv. I tried your code with a slight modification and works well.

$path = 'C:\Users\Book22.xlsx'
$xl = New-Object -ComObject excel.application
$xl.visible = $false
$wb = $xl.Workbooks.open($path)
$ws1 = $wb.WorkSheets[1]
$ws2 = $wb.WorkSheets[2]
$maxrows = $ws1.UsedRange.Rows.Count
for($row = 2;$row -le $maxrows;$row++){
    if(!($ws1.Rows[$row].Cells[6].Value2 -eq $ws2.Rows[$row].Cells[5].Value2))
  {
        $ws1.Range("$($Row):$($Row)").interior.colorindex=3
    } 
}
$wb.Save()
$wb.Close()
$xl.exit

However, now if I run the same loop to compare column in Sheet2 to the column in Sheet1 and Sheet1 has more rows than Sheet2, then this will also highlight the empty rows in Sheet2. However I don't want to highlight the rows which are empty, however i just want to highlight the corresponding extra rows in Sheet1 and the rows which have values but which are different in both sheets.

_{Tuesday, October 1, 2019 2:00 PM}

The above code does not compare a cell ( in a particular column) in Sheet1 against all cell values ( in a particular column) in Sheet2. For example if I am comparing the columns F in sheet1 against column G in Sheet2 and there is a value in F15 in Sheet1 which is present in column G25 in Sheet2, then it should not be marked as different.

Please advise on this.

_{Tuesday, October 1, 2019 5:28 PM}

The above code does not compare a cell ( in a particular column) in Sheet1 against all cell values ( in a particular column) in Sheet2. For example if I am comparing the columns F in sheet1 against column G in Sheet2 and there is a value in F15 in Sheet1 which is present in column G25 in Sheet2, then it should not be marked as different.

Please advise on this.

That is a completely different question and it is nit a "compare" it is a "find". For that yu would use the Excel Range.Find method.

Here is a quick example of how to use "FInd"

function Search-ExcelWorbook{
    # returns $true/False
    Param(
        [Parameter(Mandatory)]
        [System.__ComObject]$ExcelObject,
        [Parameter(Mandatory)]
        [string]$Filename,
        [Parameter(Mandatory)]
        [string]$SearchString
    )
    Try{
        $wb = $ExcelObject.Workbooks.Open($Filename)
        $wb.WorkSheets |
            ForEach-Object{
                if($_.UsedRange.Find($SearchString)){
                    Write-Verbose "SearchString found in $Filename"
                    $found = $true
                }
            }
        if($found){
            $true
        }else{
            Write-Verbose "SearchString NOT found in $Filename"
            $false
        }
    }
    Catch{
        Write-Error $_
    }
    Finally{
        if($wb){
            Write-Verbose 'Closing workbook'
            $wb.Close($false)
            # we must release this object on every call to this function
            if($wb){[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)}
        }
    }
}
# create Excel object
$xl = New-Object -ComObject Excel.Application

# search in workbooks
Search-ExcelWorbook $xl D:\test7\FuelHistory.xlsx 'Hello World' -Verbose

Get-ChildItem d:\test7\*.xlsx  |
    ForEach-Object{Search-ExcelWorbook $xl $_ 'Hello World' -Verbose}

It can be used on any Range object.

\(ツ)_/

_{Tuesday, October 1, 2019 11:26 PM}

Thanks for the reply. I get how the above code is working. However my search string is not single or static. I want to find all cell values in a column in one sheet in all cell values in a column in another sheet. This is where I am getting stuck.

_{Tuesday, October 1, 2019 11:49 PM}

You have to find them one cell at a time in the first sheet and use "Find" to find all cells in the second sheet - in a loop to process all of the first sheet.

\(ツ)_/

_{Thursday, October 3, 2019 1:28 PM}

Thanks again. I will try it out.

Share via

Use powershell to compare columns in excel

Question

All replies (7)

Additional resources