Monday, July 16, 2012

Finding Duplicates and copying Excel formula to the end of data

I have an excel file that has about 100,000 records. I wanted to find out the duplicates in a particular column before removing them. So I had to mark the rows with something as an identifier. Below is what I found for the formula. So essentially if the duplicates are in Column A, this formula should be placed in an empty column(or a new column):

=IF(COUNTIF(A:A, A1)>1, "Duplicate !","")

I had to apply this formula to all the 100,000 records, and someone over the internet posted a nice example of doing this:

Very useful in those time crunches !