Welcome to WebmasterWorld Guest from 126.96.36.199
Forum Moderators: phranque
If you have a column.. for example 10 items long, in column A
and you paste the following into cell B1
and then copy that cell into a column in B, one cell short of the column in A.. (ie B1 to B9) then this should iterate the formula down to
you can then sum the total of the list in column B = number of duplicates in A.
Actually, Excel is a pretty powerful data analysis tool, although I agree for duplicate finding it leaves something to be desired. I use an add-in called digDB that really improves duplicate handling in Excel. And unlike most such tools, there is a free trial available.
Select your column, including heading.
Data - filter - advanced filter - copy to another location - unique records only.
Click in the Copy To box and clear it if it has anything in it, click on an empty cell (and make sure its at row one).
That will select all the uniques and paste them to the new location. Subtract that number from your total records.
The Data > Filter > Advanced Filter > Copy to another location > Unique doesn't actually tell you what's unique, just gives you a list without duplicates.
Couldn't live without that little gem.