Remove Duplicates

I'm having a problem deleting duplicates from list in excel. I’ve attached a sample. I’ve tried the following:

1- Advanced Filter, Unique Records Only
2- Remove Duplicates function in Excel 07.
3- Pivot Table
4- Colour Conditional Formatting, sorting by colour
5- B2=IF(A2=A3,”Dup”,”Not-Dup”). The entire column returns “Not-Dup”
6- I’ve tried to resolve using the fix shg & teylyn suggested to Hillto in this thread, but am unable to get the ‘Numeric’ Keypad to appear in the ‘Find’ Function. (http://www.excelforum.com/excel-prog...uplicates.html)

Can anyone please let me know if I have the same problem as Hillto had? If not, any idea what the problem is?

thanks in advance!

Hi meggles
The Numeric key pad is the numbers keys grouped togeather not the ones running across the top of the letters
in find place the curser in the find text box the Alt and 0010

where are the duplicates in the list?

Oh... I forgot about the white spaces
use

Option Explicit
Sub ptestu()
    Dim p!, i!, k(), e
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        e = .Value
    End With
    ReDim k(UBound(e, 1), 1)
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(e, 1)
            If Not IsEmpty(e(i, 1)) Then
                e(i, 1) = Trim(e(i, 1))
                If Not .exists(e(i, 1)) Then
                k(p, 0) = e(i, 1)
                  .Add e(i, 1), p
                  p = p + 1
                  End If
         End If
        Next
       End With
Range("B1").Resize(p, 1).Value = k
 End Sub

Hi meggles
in that case
copy the code from forum
open the excel workbook to desired worksheet

press the "Alt" button plus the "F11" button for the "visual basic editor"
go to "insert" ( Top toool bar) > then "Module"

click in the white space and paste
click any where in the code and then click "run"

Option Explicit
Sub ptestu()
    Dim p!, i!, k(), e
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        e = .Value
        ReDim k(UBound(e, 1), 1)
        With CreateObject("Scripting.Dictionary")
            .CompareMode = vbTextCompare
            For i = 1 To UBound(e, 1)
                If Not IsEmpty(e(i, 1)) Then
                    e(i, 1) = Trim(e(i, 1))
                    If Not .exists(e(i, 1)) Then
                        k(p, 0) = e(i, 1)
                        .Add e(i, 1), p
                        p = p + 1
                    End If
                End If
            Next
        End With
        .ClearContents
    End With
    Range("a1").Resize(p, 1).Value = k
End Sub

your weclome
its nothing specila just regurgitated jindon code

check the formula it is offset one -one row
should be
b2=SUMIF(Sheet2!A:A,A2,Sheet2!B:B)

the white spaces again

Sub ptwo()
Dim p!, i!, k(), e
With Sheets("Raw Data").Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        e = .Value
    p = .Rows.Count
 
 ReDim k(UBound(e, 1), 1)
For i = 1 To UBound(e, 1)
  k(i, 1) = Trim(e(i, 1))
 Cells(i, 1) = k(i, 1)
Next
End With
End Sub

didnt shg say

you need to look at the import code to nip them in the bud

arr... you will have to select the correct worksheet for the macro

or add

 worksheet ("DeDuped Data").activate

and the other
worksheet ("Raw").activate

no problems it takes a while to get the hang of it
this one is for extraction the duplicates and removes the spaces

Option Explicit
Sub ptestu()
    Dim p!, i!, k(), e
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        e = .Value
        ReDim k(UBound(e, 1), 1)
        With CreateObject("Scripting.Dictionary")
            .CompareMode = vbTextCompare
            For i = 1 To UBound(e, 1)
                If Not IsEmpty(e(i, 1)) Then
                    e(i, 1) = Trim(e(i, 1))
                    If Not .exists(e(i, 1)) Then
                        k(p, 0) = e(i, 1)
                        .Add e(i, 1), p
                        p = p + 1
                    End If
                End If
            Next
        End With
        .ClearContents
    End With
    Range("a1").Resize(p, 1).Value = k
End Sub

this one is to clean the duplicated raw data
it was for your calculation as they woudnt sum while the spaces

Sub ptwo()
Dim p!, i!, k(), e
With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 1)
        e = .Value
    p = .Rows.Count
 
 ReDim k(UBound(e, 1), 1)
For i = 1 To UBound(e, 1)
  k(i, 1) = Trim(e(i, 1))
 Cells(i, 1) = k(i, 1)
Next
End With
End Sub

Can you zip it and Ill have a look tomorrow
try when the in the raw data sheet run the macro the macros only work in the open work book and you may have deduped both lists

Meggles
You have moved the columns
and now are using one worksheet
You need to change the code when you change the columns

Sub ptwo()
Dim p!, i!, k(), e
With Range("F1", Range("F" & Rows.Count).End(xlUp)).Resize(, 1)' eg the range to work with coumn F
        e = .Value
    p = .Rows.Count
 
 ReDim k(UBound(e, 1), 1)
For i = 1 To UBound(e, 1)
  k(i, 1) = Trim(e(i, 1))
 Cells(i, 6) = k(i, 1)
Next
End With
End Sub

Where do you copy the data from ????? as the white spaces go on and on and on and on and on

I would look at how you import the data to rid them you start

afternoon meggles
yer .. dont worry about then .. its just that the loop is very slow

Have you tried the adjustment??

meggles
this will do the trick

Worksheets("Sheet1").Range("A1:A100").RemoveDuplicates Columns:=1