How To: Find & extract duplicate records from a data set

Find & extract duplicate records from a data set

This video is about finding duplicate entries in excel sheet. The example excel sheet contains different names. There are around 1000 different names in the sample excel sheet. CountIf(range,criteria) formula has been used to do the comparison and return of results in True or False mode. Type =CountIf( now move your left arrow key, this will generate the formula like =CountIf(A2. Now press Ctrl+Shift+Down arrow, this will select all the available values in the column. Now press F4 to lock it and the formula will be =CountIf($A$2:$A$1001. Now we have to provide the criteria, for this we need to compare the value available in the left cell with the entire list. So update the formula as =CountIf($A$2:$A$1001,A2). This will return 1 if the value is not repeated. It will return 2, 3, 4... based on the number of duplicates. Double click the small + symbol available in the end of a cell. This will update the formula to the entire column range. We can update the formula to get the results in True or False method. Update the formula in the 1st cell as =CountIf($A$2:$A$1001,A2)>1 this will return a True if the compared value presents more then 1 time. Now Sort the column with names 1st and then column with values. Right Click -> Sort A to Z. Select the values which has true on the adjacent. Select Filter on the menu and Advance filter. This will pre populate the selected range. Now enable checkbox unique records only and select copy to another column. This will now place only the unique records on the duplicate list.

Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.

Buy Now (97% off) >

Other worthwhile deals to check out:

Join the Next Reality AR Community

Get the latest in AR — delivered straight to your inbox.

Be the First to Comment

Share Your Thoughts

  • Hot
  • Latest