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.
Have an iPhone? Check out all 200+ new features coming in iOS 13.
Be the First to Comment
Share Your Thoughts