Remove/Clear Duplicate Cells

dodger7 · Apr 15th 2008

Hi Folks

I wonder if anyone can point me in the right direction. I have a workbook with 12 sheets, 1 for each month of the year. Each month has around 30,000 user IDs on it (this is a log of each time a user logs into our website).
What I need to do is find out how many unique users have logged into our site for the whole year, or in other words, remove duplicate user ids accross the workbook.
I realise that this will probably destroy my processor, so was wondering the best way to do it?
I also have all this information in ONE spreadsheet if it would be easier? I just copied and pasted each month into a column (Jan is column A, feb in B, mar in C etc)

I ran some code on this sheet to remove dupllicates from column A:E but this was taking AGES!

Thanks in advance for any help

Jamie

**Ger Plante** · Apr 15th 2008

Re: Remove Duplicates

Hi Jamie, thats a lot of data, no wonder it was taking ages!!!

::D

Personally, I would put all the data into one sheet like you did (Columns A:L) and use a Pivot table. Let the Pivot do the work for you.

Running code on that number of Rows will be a headache, but there are quick-ish ways to do it. Can you post the code that you currently have?

Ger

sandyboath · Apr 15th 2008

Re: Remove Duplicates

Hi Jamie, I had a similar problem and found using the Advance filter option and putting the results into a different area works for me

dodger7 · Apr 15th 2008

Re: Remove Duplicates

Hi Ger

Thanks for your reply. The code I have was from ozgrid search, remove duplicates. here it is:

Code

Sub KillDupes()
Dim rConstRange As Range, rFormRange As Range
Dim rAllRange As Range, rCell As Range
Dim iCount As Long
Dim strAdd As String


    On Error Resume Next
    Set rAllRange = Selection
        If WorksheetFunction.CountA(rAllRange) < 2 Then
            MsgBox "You selection is not valid", vbInformation
            On Error GoTo 0
            Exit Sub
        End If




    Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)
    Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)


    If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then
        Set rAllRange = Union(rConstRange, rFormRange)
    ElseIf Not rConstRange Is Nothing Then
        Set rAllRange = rConstRange
    ElseIf Not rFormRange Is Nothing Then
        Set rAllRange = rFormRange
    Else
        MsgBox "You selection is not valid", vbInformation
        On Error GoTo 0
        Exit Sub
    End If
   
    Application.Calculation = xlCalculationManual


    For Each rCell In rAllRange
        strAdd = rCell.Address
        strAdd = rAllRange.Find(What:=rCell, After:=rCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Address
          
        If strAdd <> rCell.Address Then
            rCell.Clear
        End If
    Next rCell


  
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

Display More

I may have a look at pivot tables, thanks

**Ger Plante** · Apr 15th 2008

Re: Remove Duplicates

Hi Jamie, I just a quick look at Pivot tables and I dont know if they will create the effect you are looking for....

Assume for a second your data is in 12 columens on one sheet.

I used this code below just now on 65,000 rows of Data x 12 columns wide and it took about 45 seconds to extract all unique records in 12 equivalent columns on Sheet 2. It uses advanced filter.

Assumes your data starts in A1.

Code

Public Sub Rem_Duplicates()


'Assumes Data is starting in A1


Dim my_range As Range
Dim my_Col As Variant


With ActiveSheet
    Set my_range = .Range(Cells(1, 1), Cells(.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row, .Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column))
End With


Application.DisplayAlerts = False
Application.Calculation = xlManual
Application.ScreenUpdating = False


Sheets("Sheet2").Rows.Delete


Sheets("Sheet2").Select
For Each my_Col In my_range.Columns
    my_Col.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, my_Col.Column), Unique:=True
Next my_Col


Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True




End Sub

Display More

Hopefully this will get you started.

HTH
Ger

EDIT: I would add that I just re-run this code with the same number of rows and Columns with less random data (which increased the number of duplicates dramatically) and it completed the task in about 2 seconds.... so the more duplicates you have, the quicker it will work.

dodger7 · Apr 16th 2008

Re: Remove/Clear Duplicate Cells

Hi Ger

I will run this when I get to work and let you know how I get on.
Thank you for your input

Jamie[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Ger

The code run very quickly (1 minute exactly) however its not working as expected.
The code returned around 25,000 unique ids (cant remember exact figure) however I know this is incorrect because;

each column is a unique list, i.e there is no repeat of a user id in the same column. Each column averages around 35,000 user IDs so I would expect this to be the lowest figure returned from the code. To clarify, each column represents the Unique IDs logged in for that month.

Thanks
Jamie[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Ger

Remove/Clear Duplicate Cells

Participate now!

Share

Users Viewing This Thread