Excel XP Remove Duplicate Rows

[vaneagle]

Hi,

I have several excel spreadsheets with around 15 to 20 sheets with data I have extracted at around 30,000 lines per sheet. I need to remove the duplicate rows. The duplicate data sits in column B. I looked at autofilter and its not really an option given the number of files and sheets I need to look at. Is there anyway where vba may be able to help?

Data:

Row	Col B	Col C
1	217.1	217.1
2	217.1	217.1
3	231.1	231.1
4	231.1	231.1
5	448.2	448.2
6	142.56	142.56
7	142.56	142.56
8	142.56	142.56
9	119.52	119.52
10	690.46	690.46
11	1465.69	1465.69
12	783.07	783.07
13	2535.9	2535.9
14	176.55	176.55
15	968.55	968.55

any ideas?

vaneagle

 

[TonyJollans]

Hi vaneagle,

Whilst I could certainly write some code to do it, I would suggest you look at the Advanced Filter which has a Unique Records Only option.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

and do a search inthe archives for "Remove Duplicates" - there should be a wealth of info there

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[vaneagle]

Hi Guys,

TonyJollans - i tried that method following a post in this forum and I could not get it to work...as I was thinking of recording it then make the neccessary adjustments...

xlbo - I will give the forums another search...

thanks guys...

vaneagle

 

[RDC]

Did you ever get this to work? I believe I am having the same difficulty.. pls. help.. I highlighted entire table, selected Toolbar/Data/Advanced Filter, highlighted entire table for 'List range', highlighted entire table for 'Criteria range' checked 'Unique Records Only' option, 'ok'.. and it only removed 90% of the duplicates.... Am I incorrect in setting 'Criteria range'? In my case EVERY field IS the criteria.. Please help..

 

[taupirho]

Manually on a sheet by sheet basis you would do the following:-
In another column/cell eg D1 enter the formula =if(B1 = B2,0,1)
Copy this cell down. The last row in the duplicate grouping will be a one, all others will be a zero. Copy/paste special column D to remove formulas then sort on it . Now remove all the rows where column D contains a 0. Shouldn't be too hard to automate this

 

[TonyJollans]

Hi RDC,

If you just want a deduplicated list, check Unique Records Only and leave the Criteria blank.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[KenWright]

The following will run through your workbook and dedupe each sheet based on the entire table on that sheet:-

Sub FilterSheets()

Dim SrcSht As Worksheet
Dim SrcShtlrow As Long
Dim SrcShtlCol As Long
Dim SrcRng As Range
Dim NewSht As Worksheet
Dim x as long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For x = 1 To Sheets.Count

    Set SrcSht = Sheets(1)
    Currname = Sheets(1).Name
    
    SrcShtlrow = SrcSht.Cells(Rows.Count, "A").End(xlUp).Row
    
    SrcShtlCol = ActiveSheet.UsedRange.Column - 1 + _
                 ActiveSheet.UsedRange.Columns.Count
    
    Set NewSht = Worksheets.Add
    NumShts = Sheets.Count
    NewSht.Move After:=Sheets(NumShts)

    SrcSht.Activate
    With SrcSht
        Set SrcRng = .Range(Cells(1, "A"), Cells(SrcShtlrow, SrcShtlCol))
        SrcRng.AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=NewSht.Range("A1"), Unique:=True
    End With

    Sheets(1).Delete
    NewSht.Name = Currname
    
Next x

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------