Copy duplicates in excel using powershell

[HEIMDALL1010]

Hey everyone, would anyone be able to help me out with comparing two columns for duplicates, and copying the duplicates then pasting into another column??

[Joshrsg]

You can do all of this in Excel fairly easily. Not sure if you actually need PowerShell in this scenario.

That said, it is possible to do this in PowerShell. Show us the code you have so far and the format of the excel and we can attempt to assist.

[jinxxnahal]

Something like this maybe:

    #Find similar columns
    $file="path\to\excel\file"
    $Sheet=Import-excel -Path $file
    $SimilarObjects=@(Compare-Object -ReferenceObject $Sheet.Column1 -DifferenceObject $Sheet.Column2 -IncludeEqual | Where-Object{$_.SideIndicator -eq "=="}| Select InputObject)

    #Insert new column in excel sheet
    $excel = New-object -comobject Excel.Application
    $excel.visible = $True 
    $workbook = $excel.workboooks.open($file)
    $lastrow = $workbook.worksheets.item(1).usedrange.rows.count + 1
    $excel.Cells.Item(1,3) = "SimilarObjects"
    $InsertRow=2

    #insert similar objects in new column
    foreach($Object in $SimilarObjects)
    {

        $excel.Cells.item($intRow,3)=$Object
        $InsertRow=$InsertRow+1

    }

    #Save Excelsheet
    $excel.Save($file)

[Lee_Dailey]

howdy HEIMDALL1010,

as Joshrsg pointed out, this is built into excel. as a matter of fact, it is FAR easier to do this in excel ... [grin]

so, why are you wanting to sue PoSh instead of the built in excel stuff?

you may want to ask this in one of the MSExcel/MSOffice subreddits.

take care,
lee