CODEWITHSUNDEEP
excelpowershell
Sandy
Sandy

Reputation: 91

Compare Column in 2 excel and If matches, copy the respective other column value from 2nd excel to 1st excel

I have these below excel files where I have to compare the "host" and if the excel-1 value matches excel-2 then I have to create a column in excel-1 and copy the "HostGroup" from excel-2 to excel-1.

In excel-1 column in "Host"

enter image description here

and excel-2 "Host_Name"

enter image description here

e.g. excel-1 

Host

abc
def

excel-2

group     Host_name

grp1      plq
grp2      def
grp3      abc

Final output in excel-1

Group   host

grp3    abc
grp2    def

Please need some idea how to do this

Upvotes: 0

Views: 375

Answers (1)

f6a4
f6a4

Reputation: 1782

This should work:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$excel               = New-Object -ComObject Excel.Application
$excel.Visible       = $false
$excel.DisplayAlerts = $false 

$wb1                 = $excel.Workbooks.Open( "C:\excel1.xlsx", [System.Type]::Missing, $false ) 
$wb2                 = $excel.Workbooks.Open( "C:\excel2.xlsx", [System.Type]::Missing, $true ) 

$ws1                 = $wb1.WorkSheets.item(1)
$ws2                 = $wb2.WorkSheets.item(1)

[void]$ws1.Range( 'A:A' ).EntireColumn.Copy()
$insertRange = $ws1.Range( 'A:A' ).EntireRow
[void]$ws1.Range( 'A:A' ).Insert( [Microsoft.Office.Interop.Excel.XlInsertShiftDirection]::xlShiftToRight )
[void]$ws1.Range( 'A:A' ).EntireColumn.ClearContents()
$ws1.Cells( 1, 1 ).Value2 = 'GROUP'

$searchRange = $ws1.Range( 'B:B' ).EntireColumn

$lineCounter = 2
while( $ws2.Cells( $lineCounter, 1 ).Value2 ) {

    $hostName     = $ws2.Cells( $lineCounter, 2 ).Value2.Trim('" ')
    $searchResult = $searchRange.Find( $hostName, [System.Type]::Missing, [System.Type]::Missing, [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole, [Microsoft.Office.Interop.Excel.XlSearchOrder]::xlByColumns )

    if( $searchResult ) {
        $group = $ws2.Cells( $lineCounter, 1 ).Value2
        $ws1.Cells( $searchResult.Row, $searchResult.Column - 1 ).Value2 = $group
    }
    $lineCounter++
}

[void]$wb2.Close()
[void]$wb1.SaveAs("C:\excel_new.xlsx")
[void]$wb1.Close()
[void]$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

Upvotes: 1

Related Questions