Discussion on:
View:
Show:
In Excel versions 2007 and later, it is much easier to find duplicates using Conditional, Formatting, Highlight Cells Rules, Duplicate Values. After select the row(s) or column(s) desired and using the process above, use the column filter and filter for cell color or whatever you criteria indicated.
Just use the function in Excel (at least in 2010+ versions)... Maybe this column needs a bit of an update (at least the pictures?).
Useless reposting of an old article that does not apply to the 2 or 3 most recent versions of Excel.
I also find this utility a life saver:
http://www.asap-utilities.com/
I also find this utility a life saver:
http://www.asap-utilities.com/
I work with large data sets in excel. If I'm looking for a duplicate, usually I just need to find it in place without filtering. First I sort the data on the column, click the first cell in the column, then run a small utility macro.
Sub NextAlike()
' This macro moves the active cell down until it finds a duplicate.
varSwitch = False
Do Until varSwitch = True
Item = ActiveCell
ActiveCell.Offset(1, 0).Range("A1").Select
NextItem = ActiveCell
If NextItem = Item Then
ActiveCell.Offset(-1, 0).Range("A1").Select
varSwitch = True
End If
Loop
End Sub
Sub NextAlike()
' This macro moves the active cell down until it finds a duplicate.
varSwitch = False
Do Until varSwitch = True
Item = ActiveCell
ActiveCell.Offset(1, 0).Range("A1").Select
NextItem = ActiveCell
If NextItem = Item Then
ActiveCell.Offset(-1, 0).Range("A1").Select
varSwitch = True
End If
Loop
End Sub
Why don't we use the built-in feature in Office 2007 or 2010? There has been several articules showing how to do it already. I have found it intesting why all the sudden this old tips should be re-surfaced, and people liking it.
There's a note at the beginning of the article that identifies it as an old article from 2009. I didn't repost it, Techrepublic.com did -- once they buy these tips, they own them and can reuse as they see fit.
Finding duplicates is almost a non-issue in the ribbon versions -- much easier, depending on how you need to see the data and what you want to do with it once you find the duplicates. However, the methods discussed might still be valuable when the new duplicate-finding features aren't adequate. But, by all means, use the easiest method!
Finding duplicates is almost a non-issue in the ribbon versions -- much easier, depending on how you need to see the data and what you want to do with it once you find the duplicates. However, the methods discussed might still be valuable when the new duplicate-finding features aren't adequate. But, by all means, use the easiest method!
As for people's comments so far.. there will always be more than one way of "skinning a cat" or in this case finding duplicates in Excel. To me the important part is understanding how you get there rather than the end result because you can use that knowlege for other things. The countif function is one that I'll most likely use again after reading this article.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
