Find duplicate records in excel 2007
Formula in cell G3:
Copy cell G3 and paste down as far as needed.
How the formula works in cell G13
Step 1 - Understand how relative and absolute cell references work
The formula uses absolute and relative cell references. In cell G3 the formula is:
=IF(COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1, "Duplicate", "")
In cell G13 the formula has changed to:
=IF(COUNTIFS($B$3:B13, B13, $C$3:C13, C13, $D$3:D13, D13, $E$3:E13, E13)>1, "Duplicate", "")
Relative and absolute cell references
Step 2 - Find duplicates
=IF(COUNTIFS($B$3:B13, B13, $C$3:C13, C13, $D$3:D13, D13, $E$3:E13, E13)>1, "Duplicate", "")
becomes
=IF(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, "Duplicate", "")
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
=IF(2>1, "Duplicate", "")
becomes
=IF(TRUE, "Duplicate", "") and returns Duplicate in cell G13
Download excel sample file
Find duplicate records.xlsx
(Excel 2007 Workbook *.xlsx)
Functions:
IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria
Related posts:
Filter unique distinct row records in excel 2007
Filter and highlight duplicate column records in excel 2007
Filter duplicate rows in excel 2007
Recommended categories
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 code
Press 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 function
Example,
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