none
Finding Duplicates In a Row With Another Row In A Different Tab RRS feed

  • Question

  • Hello, 

    I'm currently trying to find duplicate values within one row with columns A and F. That if A and F in row 1 (Sheet1) match another row in (Sheet2) with column A and F matching it colors it red. I have an example as an attachment. Last, is there a way to overwrite the duplicates that are colored red with the 1st duplicate it finds and so on? I was able to match duplicate columns in different tabs and colored them red, but this has me stumped. Please help!! 

    Friday, April 7, 2017 11:30 PM

All replies

  • Hi Rondell309,

    With my poor English, I understand your request is like this:
      (Sheet1: column A) = (Sheet2: column A) AND
      (Sheet1: column F) = (Sheet2: column F)
       then (Sheet2: A = red, Sheet2: F = red) 

    Is it right?
    If not, please explain your needs simply. I hope you will provide sample sheet via cloud storage such as OneDrive, Dropbox, etc.

    Regards,
    Ashidacchi

     
    Saturday, April 8, 2017 7:14 AM
  • Hi Rondell309,

    According to your description, I think you want to find duplicates and color them red. Then you want to overwrite all the range colored with red with first duplicate row value, right? You could save the value when you first find them duplicate.

    Here is the example.

    Function test()
    Dim index As Integer
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng As Range
    Dim A, F As Variant
    Dim flag As Boolean
    flag = False
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    For index = 1 To ws1.UsedRange.Rows.Count
    If ws1.Cells(index, 1) = ws2.Cells(index, 1) And ws1.Cells(index, 6) = ws2.Cells(index, 6) Then
    ws1.Cells(index, 1).Interior.Color = vbRed
    ws1.Cells(index, 6).Interior.Color = vbRed
    
       If flag Then
       ws1.Cells(index, 1) = A
       ws1.Cells(index, 6) = F
       Else
       A = ws1.Cells(index, 1)
       F = ws1.Cells(index, 6)
       flag = True
       End If
    End If
    Next
    End Function
    

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 10, 2017 8:56 AM
    Moderator
  • It doesn't allow me to post a picture. They are still verifying my account it says.

    Yes, that is very close. If, Row 2 values (general numbers) Column A and Column F on sheet 1 matches values on Row 15 Column A and Column F on sheet 2 that it will color both rows on sheet1 and sheet2 red. I have used the following code to color column A red if the number values match on another sheet. Now i'm looking for values more specific of what I'm directly looking for. 

    Sub Highlight_Duplicate()

    Dim loop_sheet As Integer

    Dim sheet_usedrow As Long

    Dim loop_row As Long

    Dim compare_value As String

    Dim loop_row_first_sheet As Long

    Dim first_row_count As Long

    Dim column_number As Integer

    column_number = 1 ' Change the column_number as the column which you want to highlight.

    first_row_count = Sheets(1).UsedRange.Rows.Count

    For loop_row_first_sheet = 2 To first_row_count

    compare_value = Sheets(1).Cells(loop_row_first_sheet, column_number)

        For loop_sheet = 2 To 6

            sheet_usedrow = Sheets(loop_sheet).UsedRange.Rows.Count

            For loop_row = 2 To sheet_usedrow

                If Sheets(loop_sheet).Cells(loop_row, column_number) = compare_value Then

                    Sheets(loop_sheet).Activate

                    ActiveSheet.Cells(loop_row, column_number).Select

                    With Selection.Interior

                        .Pattern = xlSolid

                        .PatternColorIndex = xlAutomatic

                        .Color = 255

                        .TintAndShade = 0

                        .PatternTintAndShade = 0

                    End With

                End If

            Next

        Next

        Sheets(1).Activate

        ActiveSheet.Cells(loop_row_first_sheet, column_number).Select

        With Selection.Interior

            .Pattern = xlSolid

            .PatternColorIndex = xlAutomatic

            .Color = 255

            .TintAndShade = 0

            .PatternTintAndShade = 0

        End With

       

    Next

    End Sub


    • Edited by Rondell309 Monday, April 10, 2017 6:42 PM
    Monday, April 10, 2017 5:53 PM
  • Hello,

    For your code, i get the result:

    I think you just want to highlight "3/4/5/6" in Sheet2.

    >>to overwrite the duplicates that are colored red with the 1st duplicate it finds and so on

    Do you want all the "3/4/5/6" are changed into "3" since "3" is the 1st duplicate?

    To avoid misunderstanding, i suggest you share a sample file with your current data and expected result via OneDrive. To share OneDrive files, please visit Share OneDrive files and folders

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 13, 2017 12:06 PM
    Moderator