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.
Other worthwhile deals to check out:
- 97% off The Ultimate 2021 White Hat Hacker Certification Bundle
- 98% off The 2021 Accounting Mastery Bootcamp Bundle
- 99% off The 2021 All-in-One Data Scientist Mega Bundle
- 59% off XSplit VCam: Lifetime Subscription (Windows)
- 98% off The 2021 Premium Learn To Code Certification Bundle
- 62% off MindMaster Mind Mapping Software: Perpetual License
- 41% off NetSpot Home Wi-Fi Analyzer: Lifetime Upgrades
Be the First to Comment
Share Your Thoughts