Got a big file in excel and lots of mistakes, duplicates or triplicates entries? well, its not a headache anymore. In this step by step guide, we will show you how to find duplicates (or triplicates) in MS Excel and remove or replace them easily.
Find Duplicates
The below example teaches you how to find duplicates (or triplicates) in Excel.
2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Duplicate Values.
3. Select a formatting style and click OK.
Result. Excel highlights the duplicate names.
Note: select Unique from the first drop-down list to highlight the unique names.
As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), quadruplicates (if we have any), etc. Execute the following steps to highlight triplicates only.
5. Select the range A1:C10.
6. On the Home tab, click Conditional Formatting, New Rule.
7. Select "Use a formula to determine which cells to format".
8. Enter the formula =COUNTIF($A$1:$C$10,A1)=3
9. Select a formatting style and click OK.
Result. Excel highlights the triplicate names.
Explanation: =COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3, Excel formats the cell.
Because we selected the range A1:C10 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF($A$1:$C$10,A2)=3, cell A3 =COUNTIF($A$1:$C$10,A3)=3, etc.
Notice how we created an absolute reference ($A$1:$C$10) to fix this reference.
Note: you can use any formula you like. For example, use this formula =COUNTIF($A$1:$C$10,A1)>3 to highlight the names that occur more than 3 times.
Remove Duplicates
The below example teaches you how to remove duplicates in MS Excel.1. Click any single cell inside the data set.
2. On the Data tab, click Remove Duplicates.
The following dialog box appears.
3. Leave all check boxes checked and click OK.
Result. Excel removes all identical rows (blue) except for the first identical row found (yellow).
To remove rows with the same values in certain columns, execute the following steps.
4. For example, remove rows with the same Last Name and Country.
5. Check Last Name and Country and click OK.
Result. Excel removes all rows with the same Last Name and Country (blue) except for the first instances found (yellow).
This is almost useless. As you said, this formula for finding triplicates only works if you know what value to look for, in your case, you knew to look for Sierra. If you wanted to truly search for triplicates, you would have to do this for each name. What if you had a list of 2,000 names, you going to type this formula for each name? I literally have a list of 2,000+ names with some duplicates, triplicates, and possibly more. I need to filter and find and count number of duplicates, triplicates, or quadrouplicates if there are any. How can I do that?
ReplyDeleteIf you read the explanation, it applies the formula to each cell, therefore checking for duplicates of the contents of each cell. in this example, it checks for sierra as it was the original case, but the next cell checks for kilo etc
ReplyDeleteformulla dosent work
ReplyDeleteI applied it to 64,000 lines and it worked perfect for me!
ReplyDelete