Checking For Duplicates

[kparadise]

So I have a table of records. Each row is a record that contains 5 columns of items. I created these records manually, but now i need to identify any records (rows) that contain the same values across the 5 columns. See below: the first and third records are duplicates because they contain the same 5 value regardless of which column they fall in.

	A	B	C	D	E	F
1	ITEM	ITEM	ITEM	ITEM	ITEM	DUP?
2	apple	orange	grape	dog	cat	Y
3	bug	apple	orange	cat	dog	N
4	orange	apple	dog	cat	grape	Y
5	dog	bird	banana	cat	grape	N

<tbody>
</tbody>

 

[gaz_chops]

Agreed, but based on this

the first and third records are duplicates because they contain the [B]same 5 value[/B] regardless of which column they fall in.

I read it that all 5 values must be in the same row.
But I could be wrong.

Hmmm yes you could be right.

 

[Yongle]

Here is a VBA solution
- test in a COPY of your workbook
- assumes data is in columns A:E, with headers in row1

Place code in a module
Amend "SheetName"
(After testing) automatically delete temporary sheet by remove leading apostrophe

[COLOR=#ff0000][SIZE=3]'[/SIZE][/COLOR]temp.delete

Option Explicit

Sub TestForDuplicates()
    Application.ScreenUpdating = False
    Dim temp As Worksheet, ws As Worksheet, r As Long, c As Long, lastRow As Long, concat As String
    Set ws = Sheets("[COLOR=#ff0000]SheetName[/COLOR]")
'add temp sheet
    ws.Copy After:=Sheets(Sheets.Count)
    Set temp = Sheets(Sheets.Count)
'sort by row
    lastRow = temp.Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = 2 To lastRow: Call SortByRow(temp, r): Next
'concatenate cell text
    temp.Range("F1").ColumnWidth = 100
    For r = 2 To lastRow
        concat = ""
        For c = 1 To 5: concat = concat & "|" & temp.Cells(r, c): Next c
        temp.Cells(r, 6) = concat
    Next
'test for duplicates
    ws.Range("F2:F" & lastRow).Value = "N"
    For r = 2 To lastRow
        If WorksheetFunction.CountIf(temp.Range("F:F"), temp.Cells(r, 6)) > 1 Then ws.Cells(r, 6) = "Y"
    Next r
    Application.DisplayAlerts = False
    'temp.Delete  
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Private Sub SortByRow(aSheet, aRow)
    Dim rng As Range
    With aSheet
        Set rng = .Cells(aRow, 1).Resize(, 5)
            .Sort.SortFields.Clear
            .Sort.SortFields.Add2 Key:=rng
            With .Sort
                .SetRange rng
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlLeftToRight
                .SortMethod = xlPinYin
                .Apply
            End With
    End With
End Sub

 

[jimrward]

Thanks yongle I only suggested a solution but as I was on my iPad a script wasn’t possible let alone tested

 

[kvsrinivasamurthy]

ARRAY formula in F2 then copied down.

=IF(COUNTIFS(OFFSET($A$2:$E$2,ROW($A$2:$A$5)-ROW($A$2),),$A2:$E2)>0,"Y","N")

How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.