## Filter duplicate records

In this article I will demonstrate a technique to filter duplicate records. The picture below shows you a data set in columns B to E.

Next step is to create an Excel defined table. Click a cell in the table, go to tab "Insert" on the ribbon. Click "Insert Table" button. The following dialog box appears.

Click OK button.

Type Duplicate in cell F2 and then use the following formula in cell F3:

### How the formula works in cell F13

*Step 1 - Understand how relative and absolute cell references work*

The formula uses absolute and relative cell references. In cell G3 the formula is:

=COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1

In cell G13 the formula has changed to:

=COUNTIFS($B$3:B13, B13, $C$3:C13, C13, $D$3:D13, D13, $E$3:E13, E13)>1

The cell ranges expand as the formula is copied to cells below. This lets you count the current record in above records.

**How to use absolute and relative references in excel**

Comments(12) Filed in category: Excel

*Step 2 - Find duplicates*

COUNTIFS($B$3:B13, B13, $C$3:C13, C13, $D$3:D13, D13, $E$3:E13, E13)>1

becomes

COUNTIFS({"Sample0"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample1"; "Sample1"}, B13, {"B"; "B"; "A"; "A"; "B"; "B"; "B"; "A"; "A"; "A"; "A"}, C13, {11; 11; 11; 10; 10; 10; 11; 11; 10; 11; 11}, D13, {"AA111";"AA110";"AA111";"AA111";"AA110";"AA111";"AA111";"AA110";"AA110";"AA110";"AA111"}, E13)>1

becomes

=IF(COUNTIFS({"Sample0"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample1"; "Sample1"}, "Sample1", {"B"; "B"; "A"; "A"; "B"; "B"; "B"; "A"; "A"; "A"; "A"}, "A", {11; 11; 11; 10; 10; 10; 11; 11; 10; 11; 11}, 11, {"AA111";"AA110";"AA111";"AA111";"AA110";"AA111";"AA111";"AA110";"AA110";"AA110";"AA111"}, "AA111")>1, "Duplicate", "")

becomes

=2>1

and returns TRUE in cell G13

Comments(1) Filed in category: Excel

### Filter records based on formula

**Download excel file**

Find duplicate recordsv2.xlsx

(Excel 2007 Workbook *.xlsx)

### Category: Duplicate records

Comments(0) Filed in category: Duplicate records, Excel

### Category: Table

Comments(30) Filed in category: Data validation, Drop down lists, Excel, Excel table

Comments(7) Filed in category: Charts, Drop down lists, Excel, Excel table, Maps

Comments(5) Filed in category: Charts, Excel, Excel table

Highlight duplicates in a filtered excel defined table

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]Comments(4) Filed in category: Conditional formatting, Excel, Excel table

Copy filtered excel tables in vba

Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]Comments(3) Filed in category: Excel, Excel table

### 3 Responses to “Filter duplicate records”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

How do I get the sheet names in a file to becomes the row description in a summary file.

Thxs

Shaz,

Here is an user defined function:

VBA code:

Where to put the codePress Alt-F11 to open visual basic editor

Click Module on the Insert menu

Copy and paste the code above

Exit visual basic editor

How to use user defined functionExample,

Select cell A1

Type =List_sheets() in formula bar and then press CTRL + SHIFT + ENTER

Copy cell A1 and paste it to the right

Its working...! really good solution

Thanks,

Sangam R