And one of their names with the corresponding department might be present with duplications in the following dataset. Now we may have two employees with a similar name but in different departments. In the picture below, we have an additional column now that represents the departments for all employees in an organization. We can also insert a condition and find out the duplicates accordingly for the values lying in a column. Excel Formula to Find Duplicates in One Column Based on Condition
EXCEL FIND DUPLICATES IN COLUMN HOW TO
Read more: How to Find & Remove Duplicate Rows in Excelħ. Like in the following screenshot, now you’ll get all the unique text data or names only. ➤ Select the cells containing the names and corresponding outputs.
We’re now seeing the duplicate texts without their 1st occurrences in the filtered table below. ➤ Click on the Output drop-down and unmark the first option showing the numeric value ‘1’. So, we’ve just activated the Filter buttons for our headers in the following table. ➤ Under the Home tab, select the option Filter from the Sort & Filter drop-down in the Editing group of commands.
➤ Select the entire table first including its headers. Now we’ll go through the next steps to meet our objectives in this section. In the picture below, the output data have been found by following the previous method. Filter and Delete Duplicates in One Column in ExcelĪfter applying the formula to find the duplicate values, we can filter and delete them right away. Thus the duplicate text in all next cells gets ignored until the formula reaches down that corresponding duplicate value.Ħ. So, when the formula starts to go down in the Output column, it intakes the increased number of cells up to the corresponding output cell only. In this formula, we have used the relative cell reference for the selected cell range. In the picture below, the name Fred is present thrice and in the highlighted output cells, you’re seeing the sequential numbers for all duplicates including the 1st occurrence as well. Press Enter, fill down the entire column and you’ll get the following return values. The required formula in the first output Cell C5 is: In this section, we’ll look for the duplicates with the COUNTIF function, and then it’ll return the sequential number of each occurrence of the similar text in the Output column. Find Sequence of Duplicates with Excel Formula