Remove ALL Duplicates from Excel Worksheet?

Below is a macro that will find all the unique values on your sheet and put them in one column Alt+F11 to open the VB editor Insert\Module Paste the macro in the new module edit window Back in Excel, select the column you want to have the unique values in and run the macro ------------------------------------------ Sub UniqueList() ' ' UniqueList Macro ' Macro recorded 11/19/2008 ' Dim OrigArray As Variant Application.ScreenUpdating = False ' define data range With ActiveSheet r = .UsedRange _ .Rows(.UsedRange.Rows.Count).Row c = .UsedRange.Columns( _ .UsedRange.Columns.Count).Column End With OrigArray = Range("A1", Cells(r, c)).Value Dim vAns() As Variant Dim lEndPoint As Long Dim lCount As Long Dim col As New Collection Dim sIndex As String Dim vTest As Variant, vItem As Variant lEndPoint = UBound(OrigArray) For cCtr = 1 To c For rCtr = 1 To r vItem = OrigArray(rCtr, cCtr) 'if error occurs, element already exists sIndex = CStr(vItem) 'first element, add automatically If rCtr = 1 And cCtr = 1 Then col.Add vItem, sIndex ReDim vAns(1 To 1) As Variant vAns(1) = vItem Else On Error Resume Next col.Add vItem, sIndex If Err.Number = 0 Then lCount = UBound(vAns) + 1 ReDim Preserve vAns(1 To lCount) vAns(lCount) = vItem End If End If Err.Clear Next rCtr Next cCtr Range(Cells(1, ActiveCell.Column), _ Cells(lCount, ActiveCell.Column)).Value _ = Application.WorksheetFunction. _ Transpose(vAns) Application.ScreenUpdating = True End Sub -----------------------------------------

It sounds like you're not doing a row-by-row comparison, and instead just want to see the unique entries regardless of which column or row the value is. Is that right? The columns mean nothing, right? If that's the case, then in Excel 2003, with that much data, this would be a nightmare. The only thing I can think of is a macro that steps through the cells. I could write one for you but it might take me a while and I'd have to see a sample of the actual file to test it. In Excel 2007, there is an option called "Remove Duplicates" and gievs you exactly what you're asking for. If you just want to do a row-by-row comparison and get a list of the unique rows, you can use the Advance Filter feature (from Data, Filter) and check the box that says to show Unique Records only. Hope this is helpful, -David Applied Office - Microsoft Office Training - www.appliedoffice.net

If each and each sheets have precisely the comparable those with comparable identity's and the archives are a million row in line with id, form the two sheets so as of the id extensive variety (%. each and all of the understanding so the full sheet is appeared after and information save together) you may with out project replica from one sheet and paste into the different. you may very almost particularly use one table as a VLOOKUP table for the different sheet applying the identity numbers as analyze values. The layout of your records will inspect the passable attitude to regulate your difficulty. Seeing the worksheets could enable for fuller factors of what to do.

Go to http://www.asap-utilities.com and download and install the latest version. It's a free add in for Excel that has 50+ different functions across the board. One of the functions is to delete all duplicates in your selection, so select the whole sheet and run delete duplicates and it will even tell you how many it has deleted. It's brilliant.