Find duplicates in excel

Sam White 6 Reputation points
2021-09-21T13:45:45.49+00:00

I have many excel files as below, where I want to find the duplicate values in a column and highlight the entire row with green color. I have the below code, which is only highlighting the duplicate cells in green color. How can I highlight the entire row which has duplicate values in column B.

![$ex = New-Object -ComObject Excel.application  
$ex.visible = $false  
$wb = $ex.Workbooks.Open("C:\Temp\Book2.xlsx")  
$w1= $wb.Worksheets.item(1)  
$w1.activate()  
$r= $w1.Range("B2").EntireColumn  
$dup = $r.FormatConditions.AddUniqueValues()  
$dup.DupeUnique = 1  
$dup.Font.ColorIndex =4  
$wb.Save()  
$wb.Close()][1]  
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,322 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. Limitless Technology 39,301 Reputation points
    2021-09-21T17:12:39.163+00:00

    Hello @Sam White

    I know it may sound very intuitive, but have you tried with .EntireRow property?

    https://learn.microsoft.com/en-us/office/vba/api/excel.range.entirerow

    --If the the reply is helpful, please Upvote and Accept as answer--

    0 comments No comments

  2. Sam White 6 Reputation points
    2021-09-22T02:08:09.403+00:00

    There is no EntireRow property for $dup, the only properties of $dup are:

    PS C:\WINDOWS\system32> $dup

    Application :
    Creator :
    Parent :
    Priority :
    StopIfTrue :
    AppliesTo :
    DupeUnique :
    Interior :
    Borders :
    Font :
    Type :
    NumberFormat :
    PTCondition :
    ScopeType :

    0 comments No comments

  3. Sam White 6 Reputation points
    2021-09-27T04:10:53.18+00:00

    Any suggestions on this?

    0 comments No comments

  4. Anthony Axlen 1 Reputation point
    2021-10-01T12:09:17.923+00:00

    Hello,

    @Sam White - I've achieved this like that:

    • I've created two for loops to go thru the column with duplicated values, compare them (n and n+1) and save row number to array
    • At the end I've used foreach loop to go for all values in array and change the font color:
    • For entire row - $w1.Rows.Item($a).font.colorindex = 3 #red
    • For cell only - $w1.Cells.Item($a,2).font.colorindex = 3 #red

    Thank you.

    0 comments No comments

  5. Sam White 6 Reputation points
    2021-10-05T04:28:27.417+00:00

    @Anthony Axlen , thanks. Can you please provide the details of the for loop you created.

    0 comments No comments