## 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

What is a refererence in excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

*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

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

### Filter records based on formula

**Download excel file**

Find duplicate recordsv2.xlsx

(Excel 2007 Workbook *.xlsx)

### 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