none
Comparing two columns in two separate Excel sheets and highlighting duplicates (non-VBA) RRS feed

  • Question

  • I'm looking to compare the values of two columns, both called Name, which contains a list of names of more than 200 people. The sheets are named Attendance (Sheet 1 in the workbook) and Registered (Sheet 2). I want to compare the two columns and look for duplicates of names in Sheet 1 in Sheet 2, and I want to apply conditional formatting for every duplicate found in Sheet 2. How can I go about doing this?

    Thank you in advance. I tried looking for solutions on the Web, but they confused me or didn't work. I'm also looking to do this without VB code, if that's OK.



    • Edited by calvynius92 Friday, January 15, 2016 9:04 AM Updated question title
    Friday, January 15, 2016 3:29 AM

Answers

  • Hi,

    This is a sample result: button click before and after.
       

    How to:
      1. create a button on sheet:Registered, named [Highliting duplicates]
      2. [Developer] mode - ON
      3. double-click the button -- VBA editor will be shown
      4. copy&paste the below code
        
    Private Sub btn_Highliting_duplicates_Click()
        Dim registeredCnt As Integer
        Dim attendanceCnt As Integer
        Dim row1 As Integer, row2 As Integer
        ' --- get max row of each sheet
       attendanceCnt = Worksheets("Attendance").Range("A1").End(xlDown).Row
       registeredCnt = Worksheets("Registered").Range("A1").End(xlDown).Row
        ' ---
        For row1 = 1 To registeredCnt
            Worksheets("Registered").Cells(row1, 1).Interior.ColorIndex = xlNone    ' -- delete color
            For row2 = 1 To attendanceCnt
                If Worksheets("Registered").Cells(row1, 1).Value = Worksheets("Attendance").Cells(row2, 1).Value Then
                    Worksheets("Registered").Cells(row1, 1).Interior.ColorIndex = 4 ' -- 4:Green,  3:Red
                End If
            Next
        Next
    End Sub
    Best regards,

    PS
      please save in local drive(PC), and unzip it.
      HighlitingDuplicates_calvynius92.zip
    • Edited by Ashidacchi Friday, January 15, 2016 8:23 AM
    • Marked as answer by calvynius92 Monday, January 18, 2016 3:45 AM
    Friday, January 15, 2016 8:14 AM

All replies

  • Hi calvynius92,

    Could you share your Excel-file, via OneDrive, DropBox, and so on?
    If there're real names in the sheets, please modify them.

    Best regards,
    Friday, January 15, 2016 4:20 AM
  • Hi Ashidacchi

    Here's a sample of the original workbook, with random names listed (any resemblance to real persons, living or dead, is purely coincidental)

    http://1drv.ms/233HCa7

    Thanks for your help!

    Friday, January 15, 2016 7:32 AM
  • Hi calvynius92,

    I've got it, thank you.
    I'll try and hope you'll wait an hour or so.

    Best regards,
    Friday, January 15, 2016 7:39 AM
  • Hi,

    This is a sample result: button click before and after.
       

    How to:
      1. create a button on sheet:Registered, named [Highliting duplicates]
      2. [Developer] mode - ON
      3. double-click the button -- VBA editor will be shown
      4. copy&paste the below code
        
    Private Sub btn_Highliting_duplicates_Click()
        Dim registeredCnt As Integer
        Dim attendanceCnt As Integer
        Dim row1 As Integer, row2 As Integer
        ' --- get max row of each sheet
       attendanceCnt = Worksheets("Attendance").Range("A1").End(xlDown).Row
       registeredCnt = Worksheets("Registered").Range("A1").End(xlDown).Row
        ' ---
        For row1 = 1 To registeredCnt
            Worksheets("Registered").Cells(row1, 1).Interior.ColorIndex = xlNone    ' -- delete color
            For row2 = 1 To attendanceCnt
                If Worksheets("Registered").Cells(row1, 1).Value = Worksheets("Attendance").Cells(row2, 1).Value Then
                    Worksheets("Registered").Cells(row1, 1).Interior.ColorIndex = 4 ' -- 4:Green,  3:Red
                End If
            Next
        Next
    End Sub
    Best regards,

    PS
      please save in local drive(PC), and unzip it.
      HighlitingDuplicates_calvynius92.zip
    • Edited by Ashidacchi Friday, January 15, 2016 8:23 AM
    • Marked as answer by calvynius92 Monday, January 18, 2016 3:45 AM
    Friday, January 15, 2016 8:14 AM
  • Hi,

    You're also looking to do it without code/VBA.
    I have no idea to realize it without code, sorry.

    Best regards, 
    Friday, January 15, 2016 8:33 AM
  • Hi Ashidacchi, thank you so much for your help! It's OK if you did in code, I will make use of the edited file you provided.

    Also, is the button a requirement? I just need to see the comparisons immediately, without needing any external input.

    And also, how do I access the code above in the sheet? Sorry to inconvenience you.

    Thank you very, very much again for helping out. Really appreciate it :)



    • Edited by calvynius92 Friday, January 15, 2016 9:13 AM Edited content
    Friday, January 15, 2016 9:04 AM
  • Hi calvynius92,

    The button I added is not necessarily required.
    An automatic way is to handle the change of sheet:Attendance. When some cell is changed(i.e. new attendance added), the same code(in button [Highliting duplicates] is to be executed.

    It's simple, so I'll give you a file, later.

    # I think what you want cannot be done, if without code.

    Best regards,

    Friday, January 15, 2016 9:24 AM
  • Hi, 

    This is my 2nd trial.
    (When Attendance added/deleted, Registered automatically get colored)
    HighLightingDuplicates_2nd.zip

    Best regards,

    Friday, January 15, 2016 9:53 AM
  • Hi,

    > And also, how do I access the code above in the sheet?









    Best Regards,
    • Edited by Ashidacchi Friday, January 15, 2016 10:25 AM
    • Marked as answer by calvynius92 Monday, January 18, 2016 3:45 AM
    • Unmarked as answer by calvynius92 Monday, January 18, 2016 3:45 AM
    Friday, January 15, 2016 10:19 AM
  • Hi Ashidacchi, thanks again for all your help. I'll make good use of this :)
    Monday, January 18, 2016 3:45 AM