none
Powershell copy no duplicates from one column to another worksheet

    Question

  • I am trying to make a new worksheet in a excel file call contacts.xlsx and call it CallCenter using power shell. 
    I want to copy all of column I to column A in the new worksheet but no duplicates.
    I have pieced together the script below which does all except I can’t figure out how to name the new worksheet call centers nor can I get it to only copy the unique names from column I.
    I am pretty sure I need the –Unique filter and I have tried it in different locations with no success. You can see below where I have placed the pipe sort –unique.  The other places seem to cause different errors.  Where it is causes no error but does nothing.
    As far as how to name the sheet callcenters I thought the line $worksheet = $Workbook.Worksheets.add() would be it but when I put $worksheet = $Workbook.Worksheets.add(‘callcenter’) it merely gives errors.


    $path = "C:\Temp\ contacts.xlsx"
    $Excel = New-Object -ComObject excel.application
    $Excel.visible = $false
    $Workbook = $excel.Workbooks.open($path)
    $Worksheet = $Workbook.WorkSheets.item("outfile")
    $worksheet.activate() 
    $range = $WorkSheet.Range("I1").EntireColumn
    $range.Copy() | out-null
    $worksheet = $Workbook.Worksheets.add()
    $Worksheet = $Workbook.Worksheets.item('Sheet1')
    $Range = $Worksheet.Range("A1") | sort -unique
    $Worksheet.Paste($range)
    $workbook.Save() 
    $Excel.Quit()
    Remove-Variable -Name excel
    [gc]::collect()
    [gc]::WaitForPendingFinalizers()


    ronald white

    Monday, March 12, 2012 7:47 PM

Answers

  • Here is the easiest method to copy a range from opne sheet to another or even from one workbook to another.

    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $excel.Workbooks.open($path)
    $sheet1 = $Workbook.WorkSheets.item("sheet1")
    $sheet2 = $Workbook.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.Copy( sheet2.Range("A1").EntireColumn)


    ¯\_(ツ)_/¯

    • Marked as answer by tnetplus Monday, March 12, 2012 10:45 PM
    Monday, March 12, 2012 9:07 PM
  • Here is how to copy and do a unique along with a final sort usingonly Excel:

    $path = "e:\Test2\sort_unique.xlsx" 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $excel.Workbooks.open($path)
    $sheet1 = $Workbook.WorkSheets.item("sheet1")
    $sheet2 = $Workbook.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter 1, $sheet2.Range("A1"), $true
    $sheet2.UsedRange.Sort $sheet2.Range("A1").EntireColumn

    Note that 'AdcvancedFilter' allows for a filter inplace or copy option.  '2' is copy to new range.  We can use this to extract and copy only the unique data from the column.


    ¯\_(ツ)_/¯



    • Edited by jrv Monday, March 12, 2012 9:39 PM
    • Marked as answer by tnetplus Monday, March 12, 2012 10:45 PM
    Monday, March 12, 2012 9:37 PM

All replies

  • The probelm is that 'tange' is an object and not a collection.  Yu need to use range.cells and then create a new range with those cells.  This can be inserted/pasted back into Excel.

    It would e easier to just do the whole thing in Excel.  Esccute 'RemoveDuplicates' then execute a 'sort' on the column.

    To get the column call copy on the source column object.


    ¯\_(ツ)_/¯


    • Edited by jrv Monday, March 12, 2012 8:40 PM
    Monday, March 12, 2012 8:37 PM
  • thanks.

    I dont understand tange.  But i think i might see what you are saying about range cells.  I will try to look for more info on that. 

    And yes it would be easer to just do it in excell but i am hoping to do it in powershell.  I had a user for some thing like this in the past but could not do it with batch so want to see if i can in power shell.

    Thanks


    ronald white

    Monday, March 12, 2012 8:43 PM
  • Here is the easiest method to copy a range from opne sheet to another or even from one workbook to another.

    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $excel.Workbooks.open($path)
    $sheet1 = $Workbook.WorkSheets.item("sheet1")
    $sheet2 = $Workbook.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.Copy( sheet2.Range("A1").EntireColumn)


    ¯\_(ツ)_/¯

    • Marked as answer by tnetplus Monday, March 12, 2012 10:45 PM
    Monday, March 12, 2012 9:07 PM
  • Here is how to copy and do a unique along with a final sort usingonly Excel:

    $path = "e:\Test2\sort_unique.xlsx" 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $excel.Workbooks.open($path)
    $sheet1 = $Workbook.WorkSheets.item("sheet1")
    $sheet2 = $Workbook.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter 1, $sheet2.Range("A1"), $true
    $sheet2.UsedRange.Sort $sheet2.Range("A1").EntireColumn

    Note that 'AdcvancedFilter' allows for a filter inplace or copy option.  '2' is copy to new range.  We can use this to extract and copy only the unique data from the column.


    ¯\_(ツ)_/¯



    • Edited by jrv Monday, March 12, 2012 9:39 PM
    • Marked as answer by tnetplus Monday, March 12, 2012 10:45 PM
    Monday, March 12, 2012 9:37 PM
  • Many many thanks.

    I will admit i was trying something so different at this point i was not even close.  Yours is great.


    ronald white

    Monday, March 12, 2012 10:46 PM
  • When in doubt, always start with VBA.  Be sure you have installed the VBA help for Office.


    ¯\_(ツ)_/¯

    Monday, March 12, 2012 11:14 PM
  • Here is how to copy and do a unique along with a final sort usingonly Excel:

    $path = "e:\Test2\sort_unique.xlsx" 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $excel.Workbooks.open($path)
    $sheet1 = $Workbook.WorkSheets.item("sheet1")
    $sheet2 = $Workbook.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter 1, $sheet2.Range("A1"), $true
    $sheet2.UsedRange.Sort $sheet2.Range("A1").EntireColumn

    Note that 'AdcvancedFilter' allows for a filter inplace or copy option.  '2' is copy to new range.  We can use this to extract and copy only the unique data from the column.


    ¯\_(ツ)_/¯



    I hope it is not lot late to ask a follow up but the script produces error on lines 8

    Unexpected token 'filter' in expression or statement.
    At line:8 char:14

    Unexpected token '1' in expression or statement.
    At line:8 char:21

    Unexpected token ',' in expression or statement.
    At line:8 char:22

    Unexpected token 'sheet1' in expression or statement.
    At line:8 char:24

    Unexpected token '.Range' in expression or statement.
    At line:8 char:31

    Unexpected token '(' in expression or statement.
    At line:8 char:37

    Unexpected token 'I1' in expression or statement.
    At line:8 char:38

    Unexpected token ')' in expression or statement.
    At line:8 char:42


    ronald white

    Tuesday, March 13, 2012 4:25 PM
  • Sorry -

    $sheet1 = $Workbook.WorkSheets.item("sheet1")
    $sheet2
    = $Workbook.Worksheets.add()

    Should be:

    $sheet1 = $wkbk.WorkSheets.item("sheet1")
    $sheet2
    = $wkbk.Worksheets.add()

    The code was just an exampe and incomplete.  Unfortunately I copied the wrong version.  I had to PosH windows open and copied the old one.

    You also need to be sure it is copying the correct column for your sheet.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 4:32 PM
  • i am sorry i feel like i sould be able to figure this out buty it is still giiving the same error

    Unexpected token '1' in expression or statement.
    At line:8 char:20

    Unexpected token ',' in expression or statement.
    At line:8 char:21

    Unexpected token 'sheet2' in expression or statement.
    At line:8 char:23

    Unexpected token '.Range' in expression or statement.
    At line:8 char:30

    Unexpected token '(' in expression or statement.
    At line:8 char:36


    ronald white

    Tuesday, March 13, 2012 4:42 PM
  • i am sorry i feel like i sould be able to figure this out buty it is still giiving the same error

    Unexpected token '1' in expression or statement.
    At line:8 char:20

    Unexpected token ',' in expression or statement.
    At line:8 char:21

    Unexpected token 'sheet2' in expression or statement.
    At line:8 char:23

    Unexpected token '.Range' in expression or statement.
    At line:8 char:30

    Unexpected token '(' in expression or statement.
    At line:8 char:36


    ronald white

    This is the code

    $path = "C:\Temp\RealTimeUserInfo.xlsx"
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $excel.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item("outfile")
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter 1, $sheet1.Range("A1"), $true
    $sheet2.UsedRange.Sort $sheet2.Range("A1").EntireColumn


    ronald white

    Tuesday, March 13, 2012 4:44 PM
  • Can you post your exact script?

    This doesn't look at all like a PowerShell error.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 4:45 PM
  • Can you post your exact script?

    This doesn't look at all like a PowerShell error.


    ¯\_(ツ)_/¯


    $path = "C:\Temp\RealTimeUserInfo.xlsx"
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $excel.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item("outfile")
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter 1, $sheet1.Range("A1"), $true

    $sheet2.UsedRange.Sort $sheet2.Range("A1").EntireColumn


    ronald white

    Tuesday, March 13, 2012 4:49 PM
  • Sorry - I found another error.

    $path = "e:\Test2\sort_unique.xlsx" 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $xl.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item("sheet1")
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter 1, $sheet2.Range("A1"), $true
    $sheet2.Range("A1").Sort()

    The sort may fail.  It needs more parameters in PowerSHell than in VBA. 


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 4:50 PM
  • still getting same errors.

    Unexpected token '1' in expression or statement.
    At line:8 char:20

    Unexpected token ',' in expression or statement.
    At line:8 char:21

    Unexpected token 'sheet2' in expression or statement.
    At line:8 char:23

    Unexpected token '.Range' in expression or statement.
    At line:8 char:30

    Unexpected token '(' in expression or statement.
    At line:8 char:36

    Unexpected token 'A1' in expression or statement.
    At line:8 char:37

    Unexpected token ')' in expression or statement.
    At line:8 char:41


    ronald white

    Tuesday, March 13, 2012 4:58 PM
  • I can't help you if you don't post teh script you are suing and if you don't post the complete erro rmessage.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 5:05 PM
  • I can't help you if you don't post teh script you are suing and if you don't post the complete erro rmessage.


    ¯\_(ツ)_/¯

    i am sorry

    I that is the error message and the script.  The one i was using is posted above.  I will copy it below.  I got no error on mine but it copied the entire contents of the column

    $path = "C:\Temp\RealTimeUserInfo.xlsx"
    $Excel = New-Object -ComObject excel.application
    $Excel.visible = $false
    $Workbook = $excel.Workbooks.open($path)
    $Worksheet = $Workbook.WorkSheets.item("outfile")
    $worksheet.activate() 
    $range = $WorkSheet.Range("I1").EntireColumn
    $range.Copy() | out-null
    $worksheet = $Workbook.Worksheets.add()
    $Worksheet = $Workbook.Worksheets.item('Sheet1')
    $Range = $Worksheet.Range("A1") | sort -unique
    $Worksheet.Paste($range)
    $workbook.Save() 
    $Excel.Quit()
    Remove-Variable -Name excel
    [gc]::collect()
    [gc]::WaitForPendingFinalizers()

    I am currenly trying to do it with your script

    $path = "C:\Temp\RealTimeUserInfo.xlsx"
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $xl.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item("outfile")
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter 1, sheet2.Range("A1"), $true
    $sheet2.Range("A1").Sort()

    This is the error i am getting running yours

    Unexpected token '1' in expression or statement.
    At line:8 char:20

    Unexpected token ',' in expression or statement.
    At line:8 char:21

    Unexpected token 'sheet2.Range' in expression or statement.
    At line:8 char:23

    Unexpected token '(' in expression or statement.
    At line:8 char:35

    Unexpected token 'A1' in expression or statement.
    At line:8 char:36

    Unexpected token ')' in expression or statement.
    At line:8 char:38


    ronald white

    Tuesday, March 13, 2012 5:09 PM
  • I can't help you if you don't post teh script you are suing and if you don't post the complete erro rmessage.


    ¯\_(ツ)_/¯

    Am i hopeless  :) 

    I do thank you so much for trying.

    I will keep looking.  I know you have given me the answer i will figure out where i am doing it wrong.  Thanks asgain


    ronald white

    Tuesday, March 13, 2012 7:44 PM
  • The code you pasted is nothing even close to what I posted.  It is just your original code.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 10:15 PM
  • I am going to try to set this up for you one more time using a slightly differebt approach.  Please follow these instructions:

    1. Create simple speradsheet with the 'A' column filled for a few cells down with some text entries in any order. 12 or so will do.

    2. Use the script below and only change the name and location of the Excel file. ($path)

    $path = "e:\Test2\sort_unique.xlsx" 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $xl.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item("sheet1")
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.Copy($sheet2.Range("A1").EntireColumn)
    $sheet2.usedRange.sort($sheet2.Range("A1").EntireColumn,1)
    $sheet2.UsedRange.AdvancedFilter(1,"",$sheet2.Range("A1").EntireColumn, $true)

    I have runthis code in POweerShell repeatedly and it runs as expected.  There aer no errors.  A sheet is added and the 'A' column of 'sheet1' is copied to the new sheet then sorted and filtered to a unique list.

    Do not worry about closing and saving the spreadsheet.  Just use it to rerun the code until you understand how it works.  Once you understand it you can modify it into you own script.

    Using copy and paste a few lines at a time will help you to see what is happening.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 10:39 PM

  • Flawless

    Thanks so very much!!<o:p></o:p>



    It is beyond amazing so many wonderful people are willing to share there time and skills.<o:p></o:p>



    Thanks again<o:p></o:p>



    ronald white

    Wednesday, March 14, 2012 2:42 PM
  • Ronald - you wil discover that you need to compensate for the header row.  I recommend that you use a header row and that you liit the sort to not include the header row.  AdvancedFilter has no way of ignoring a header and sort will always sort everything in the specified range even if the top tow is considered a header.

    There is a way to force Sort to see the header but it is easier to just uses a rang that does not include the header.


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 5:04 PM
  • Thanks.

    i was trying to figure that out.  Also weird thing is it not only removes the duplicates as it should it removes the rows.

    so if i had 3 rows numbeered 1 2 3 with the below info after i run it i have no row 2.  It goes 1 and then 3. 

    1 Andy adems

    2 Andy Adems

    3 Billy Bright


    ronald white

    Wednesday, March 14, 2012 6:46 PM
  • Yes - it is filtering the rows.  They are hidden.  Now jsut copy the column to a new loaction and it will not have the hidden rows.  Remember this is an advanced filter.


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 6:53 PM
  • Thanks


    ronald white

    Wednesday, March 14, 2012 6:55 PM
  • Here is a different version that copies the rows at the same time it filters them:

    $path = "e:\Test2\sort_unique.xlsx" 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $xl.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item("sheet1")
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range("A1").EntireColumn
    $r1.AdvancedFilter(2,"",$sheet2.Range("A1").EntireColumn, $true)
    $r2=$sheet2.Range("A2:A12")
    $r2.Sort($r2,1)

    THis version also adjusts the sort for the header row.  You can use the UsedRange object to get the last row of the column and adjust the range to match.


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 7:07 PM
  • Do you know if uoi can do multipule columns at once

    $r1 = $sheet1.Range("I1","J1","K1").EntireColumn
    $r1.AdvancedFilter(2,"",$sheet2.Range("A1","B1","C1").EntireColumn, $true)
    $r2=$sheet2.Range("A2:A12")
    $r2.Sort($r2,1)


    ronald white


    • Edited by tnetplus Wednesday, March 14, 2012 7:25 PM
    Wednesday, March 14, 2012 7:25 PM
  • Here is a version of the demo that accounts for the header.  It took a bit to figure out wher the Microsft documentation was wrong.  The following works

    $path = 'e:\Test2\sort_unique.xlsx' 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $xl.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item('sheet1')
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range('A1').EntireColumn
    $r2=$sheet2.Range("A1").EntireColumn
    $r1.AdvancedFilter(2,'',$r2, $true)
    $missing = [type]::Missing
    $hasHeader=1
    $r2.Sort($r2,1,$missing,$missing,$missing,$missing,$missing,$hasHeader)
    $wbk.SaveAs('e:\Test2\sorted.xlsx')
    $xl.Quit() 
    while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)){'removed'}

    We also do a clean shutdown of Excel.


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 7:40 PM
  • Thanks agin. 

    I will for sure put this page in my notes.


    ronald white

    Wednesday, March 14, 2012 7:44 PM
  • I do hope i can return the favor one day.  If you ever have any questions about............

    emm...............

    hmmm...............

    Walking dead tv series. 

    Yey i will be all over it for ya


    ronald white

    Wednesday, March 14, 2012 7:55 PM
  • I do hope i can return the favor one day.  If you ever have any questions about............

    emm...............

    hmmm...............

    Walking dead tv series. 

    Yey i will be all over it for ya


    ronald white

    How about old episodes of Tombstone?


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 9:15 PM
  • Here is a more complete version with correct shutdown.  The shutdown must release all craeted objects or Excel will hang in memory.  This is why I usually don't create objects or draw references to them.

    $path = 'e:\Test2\sort_unique.xlsx' 
    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $wbk = $xl.Workbooks.open($path)
    $sheet1 = $wbk.WorkSheets.item('sheet1')
    $sheet2 = $wbk.Worksheets.add()
    $r1 = $sheet1.Range('A1').EntireColumn
    $r2=$sheet2.Range("A1").EntireColumn
    $r1.AdvancedFilter(2,'',$r2, $true)
    $missing = [type]::Missing
    $hasHeader=1
    $r2.Sort($r2,1,$missing,$missing,$missing,$missing,$missing,$hasHeader)
    $wbk.SaveAs('e:\Test2\sorted.xlsx')
    $wbk.Close()
    $xl.Quit()
    while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($r1)){'removed'}
    while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($r2)){'removed'}
    while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet1)){'removed'}
    while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet2)){'removed'}
    while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($wbk)){'removed'}
    while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)){'removed'}

    Each object is 'de-referenced' in a while loop to be sure that all references have been removed. The 'Release' with return -1 when ther eare no more references. In this example there is probably only one reference but there could bemore.  DOing it this way guaranteees that all willbe removed and the program will terminate. 

    If you want to test this just comment out a couple of teh release lines and you will see that Excel is still in memory.

    Excel 2010 may act a bit differently here but 2007 and earlier all worjk this way.


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 9:43 PM