Excel How to find duplicate cells or values in a row?

3
votes

I have a dataset in which each row belongs to a unique person so what I want to do is that find duplicate values in each row.

I tried using conditional formatting but its very time consuming as I have to apply it to each individual row otherwise it will find duplicates among all rows not just one row.

enter image description here

Could you please suggest something that can help me it can be formula or vba or formula for conditional formatting.

I used macro recorder to create a macro and the results is below. If I can make it go through a range of rows and apply the formatting that could help

Sub DuplicatesRow1() ' ' DuplicatesRow Macro '

'
    Rows("251:251").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Rows("252:252").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Rows("253:253").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("E259").Select End Sub
2
vbaexcelduplicates
this seems like a bad design flaw in Excel. You did put the excel-vba tag in here. Have you made any attempts with code to accomplish this? If so, please provide what you have tried. Otherwise, the question risks getting down-voted and closed. The other option - and perhaps less time-consuming the enter a format for each row - is to copy and paste formats from row-to-row; though this obviously is cumbersome for numerous row amounts. – Scott Holtzman
Also, if you provide an example with some sample data to elaborate on your problem, this increases your chances of getting a good answer. – Michael
@ScottHoltzman Vba code added – Ali Shaikh
@Michael Example added – Ali Shaikh

2 Answers

1
votes

I further worked on this and managed to come up with the following code which seems to be working for me. I am new to VBA and do not have enough experience so please let me know if my code can be improved further

Private Sub HighlightDuplicateRow(row As Integer)

Dim report As Worksheet
Set report = Excel.ActiveSheet
report.Cells(row, row).EntireRow.Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub


Sub DuplicatesInEachRow()
Dim counter As Integer, limit As Variant
counter = 2
limit = InputBox("Give me last row number", "Highlight Duplicates in a Row")
If limit = "" Then Exit Sub
Do Until counter > limit
Call HighlightDuplicateRow(counter)
counter = counter + 1
Loop
End Sub
0
votes

Here is a loop that will set a conditional format on each row. I used sheet and range references based on your sample data and code. You modify these to fit your exact data set.

I will also note that I am concerned about this causing performance issues in Excel if there are numerous rows, as the amount of Formats may seriously raise your file size and affect performance.

Sub LoopCF()

Dim ws As Worksheet
Set ws = Sheets("Sheet1")

'Dim lRow As Long
'lRow = ws.Range("A2").End(xlDown).Row 'will give row 200 as long as contiguous rows

Dim rng As Range, cel As Range
Set rng = ws.Range("B2:B200") 'ws.Range("B2:B" & lRow)

For Each cel In rng

    With cel.Resize(1, 4)

        .FormatConditions.AddUniqueValues
        .FormatConditions(.FormatConditions.Count).SetFirstPriority

        With .FormatConditions(1)

            .DupeUnique = xlDuplicate

            With .Font
                .Color = -16383844
                .TintAndShade = 0
            End With

            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With

            .StopIfTrue = False

        End With

    End With

Next

End Sub