Yesterday I demonstrated how to create a list of files in a folder and subfolders. Today I am going to show you how to identify duplicate files from the list we created yesterday. This can be useful if you have a large photo or mp3 collection and want to remove duplicates.

Begin with inserting a new row at the top and type describing column headers.

Count File Names

  1. Select cell D2
  2. Type =COUNTIF($B$2:$B$447, B2)
  3. Press Enter
  4. Copy cell D2 and paste to D2:D447

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition. In cell D2 the formula returns how many times "New Text Document.txt" is found in cell range B2:B447. Now we know when a file has a duplicate. But what if the duplicate files have different sizes? How do we find files with duplicate names and the same size?

Count File Names and File Sizes

  1. Select cell E2
  2. Type =COUNTIFS($C$2:$C$447, C2, $B$2:$B$447, B2)
  3. Press Enter
  4. Copy cell E2 and paste to E2:E447

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...) counts the number of cells specified by a given set of conditions or criteria. In cell E2 the formula returns the number of cells where "New Text Document.txt" and file size 0 (zero) are found. If the number 1 is returned then the record is unique.

Create a table

A table can quickly filter duplicate values.

  1. Select the list
  2. Click "Insert" on the ribbon.
  3. Click "Table" button in the table group.
  4. Click OK.

Filter column D

If you want to filter duplicate file names only, use these instructions.

  1. Click black arrow in cell D1
  2. Select "Sort Largest to Smallest"
  3. Click black arrow in cell D1
  4. Remove 1. The table filters duplicate values.
  5. Click OK.

Filter column E

If you want to filter duplicate file names and file sizes, use these instructions.

  1. Click black arrow in cell E1
  2. Select "Sort Largest to Smallest"
  3. Click black arrow in cell E1
  4. Remove 1. The table filters duplicate values.
  5. Click OK.

Recommended blog posts

Want to learn more about countif and countifs formulas?  You must read these blog posts:

Download excel sample file for this tutorial.

Identify duplicates.xlsx

(Excel 2007 Workbook *.xlsx)