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:

http://www.handyexceltips.com/2008/01/16/dragging-a-formula-down/

Very useful in those time crunches !