Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel XP Remove Duplicate Rows

Status
Not open for further replies.

vaneagle

Technical User
Apr 23, 2003
71
AU
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:
Code:
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
 
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.
 
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
 
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
 
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..

 
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
 
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.
 
The following will run through your workbook and dedupe each sheet based on the entire table on that sheet:-

Code:
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................

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]

----------------------------------------------------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top