If you use MS excel, you must be aware that its easier to figure out duplicate data in a column and delete it. To do this, you just need to select Data-> Remove Duplicates. However, what if You want to have a look before data is deleted? What if You just want to find duplicate and do not wish to remove or delete the data? In this post, we will figure out a simpler and easier way to accomplish this task.
The figure in the left has city names. As we can see some of the names are duplicate. We want to see what all city names are duplicate. Additionally, we also want to check count of duplicate appearance in the column.
In the cell B2, we will write the formula =COUNTIF(A2:A10,A2). A2:A10 is the range that we are giving followed by criteria as A2. The formula will look into the range provided to count number of times data is repeated in the column. Clearly, we can see that we have Tokyo and New York repeated 2 times and hence count of duplicate in column B5 and B7 are 2.
Headhonk 