Highlight duplicate rows on the spreadsheet

[Apple08]

Hi All

Please I have a spreadsheet with Columns A to AE, I need to find out the duplicate rows if the cells in columns B, C, D and F are matched with another row. I expect those duplicate rows should be highlighted in cell column A (not the whole row) for checking.

The spreadsheet has unknown total number of rows.

Please could anyone give me an idea how to create this macro? Many thanks.

 

[Apple08]

I have replaced the line, the macro finished very quick but Column A content is all gone without any highlighted colour, also the filter on the first row is gone too.

 

[Sanjeev1976]

Remove the line :

Range("A2:A" & lRow).ClearContents

 

[Apple08]

Column A content looks like concatenate of column B to F, The original content in A has all gone and no highlighted colour

 

[Sanjeev1976]

Can you provide some sample using XL2BB tool, so that proper solution can be provided

 

[Apple08]

Loading Google Sheets

 

[igold]

How about this. Sorry about the confusion. I thought that Post #4 was a representative sample of your data. If this works for you, code could probably be added to trigger a MsgBox that tells you which rows are matches. See if this works first, before we jump to something else... The code should take about 30 seconds to run.

Sub Find_Duplicates4()

    Dim arr, arr2, rng As Range
    Dim lRow As Long, j As Long, e As Long, i As Long
    
    Application.ScreenUpdating = False
    lRow = Sheets("Project").Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:F" & lRow)
    arr = rng
    arr2 = rng
    For i = 1 To lRow - 1
        For j = 1 To lRow - 1
            If i + 1 > lRow - 1 Then
                Application.ScreenUpdating = True
                MsgBox "Operation Complete"
                Exit Sub
            End If
            If i = j Then j = j + 1
            If arr(i, 6) = arr2(j, 6) Then
                For e = 5 To 1 Step -1
                    If arr(i, e) <> arr2(j, e) Then Exit For
                Next
                rng.Cells(((i - 1) * 5 + i)).Interior.ColorIndex = 6
            End If
        Next
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[Apple08]

Thanks igold. The macro picked up all rows with the same value in column F. However, I need to pick those duplicates having the same values in column B, C, D and F (not only F). I wonder is it possible to update the code?

Also I have added in 'MsgBox 9"Macro running is completed.") before End Sub so as to confirm the macro is completed. However only the MsgBox 'Operation Complete' appeared during the macro running.

 

[Apple08]

Thanks igold. The macro picked up all rows with the same value in column F. However, I need to pick those duplicates having the same values in column B, C, D and F (not only F). I wonder is it possible to update the code?

Also I have added in 'MsgBox 9"Macro running is completed.") before End Sub so as to confirm the macro is completed. However only the MsgBox 'Operation Complete' appeared during the macro running.

Msgbox "Macro running is completed", sorry for the typo above

 

[Sanjeev1976]

Have modified igold's code and you can try the below :

Sub Find_Duplicates4()

    Dim arr, arr2, rng As Range
    Dim lRow As Long, j As Long, e As Long, i As Long
    
    Application.ScreenUpdating = False
    lRow = Sheets("Project").Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:F" & lRow)
    arr = rng
    arr2 = rng
    For i = 1 To lRow - 1
        For j = 1 To lRow - 1
            If i + 1 > lRow - 1 Then
                Application.ScreenUpdating = True
                MsgBox "Operation Complete"
                Exit Sub
            End If
            If i = j Then j = j + 1
            If arr(i, 2) = arr2(j, 2) And arr(i, 3) = arr2(j, 3) And arr(i, 4) = arr2(j, 4) And arr(i, 6) = arr2(j, 6) Then
                For e = 5 To 1 Step -1
                    If arr(i, e) <> arr2(j, e) Then Exit For
                Next
                rng.Cells(((i - 1) * 5 + i)).Interior.ColorIndex = 6
            End If
        Next
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[Kerryx]

you could just concat the data from BCD&F into a cell and then trim and clean the values

Book1
	B	C	D	E	F	G
1	ColB	ColC	ColD	ColE	ColF	ConCat
2	2	6	6	h	7	266h7
3	3	5	6	j	8	356j8
4	f	1	4	6	o	f146o
5	g	3	6	5	h	g365h
6	3	5	6	j	8	356j8
7	1	d	3	6	f	1d36f
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=TRIM(CLEAN(CONCAT(B2:F2)))

Then just use
Excel function under Dat to remove Duplicates