1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXcel spreadheet - removing duplicate lines

Discussion in 'Business Applications' started by Berners, Jul 8, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Berners

    Berners Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    16
    I have an excel spreadsheet with 6,000 names and addresses, many of which are duplicated because each couple is listed as two individuals.

    The name field is identical for the duplicates - so is there a way of creating a macro which deletes a row if it the field is identical to the one above, please ?

    I have attached a mini sample of the data so that you can see what I mean.

    Keeping my fingers crossed that someone can help, as it will take me ages to remove them manually!

    Thanks
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do you have Access?
     
  3. Berners

    Berners Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    16
    No - sorry
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you send me the file so that I can import it in to an Access table that will remove the Duplicates.
     
  5. Berners

    Berners Thread Starter

    Joined:
    Nov 12, 2007
    Messages:
    16
    I'm afraid not - it is very personal data.
     
  6. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Try this
    Insert a row at the top of the records and give each column a Field name such as Name Address
    Then click oncell A1 (which should be the field heading Name)
    Then Data, Filter, Advanced Filter
    Select Filter list in place
    Tick the option, unique records only, then click on OK

    Then click on Edit, Goto, Special, Current Region, OK

    Then Edit, Copy

    Open a blank spreadsheet and click on A1

    Edit, paste


    You should now have only the records that are unique


    Hope that helps
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If the Villan's method doesn't work for you, on a copy try this macro

    Dim lastrow As Long, count As Integer
    lastrow = Cells(Rows.count, "A").End(xlUp).Row
    For count = lastrow To 2 Step -1
    If Cells(count, 1) = Cells(count - 1, 1) Then
    Cells(count, 1).EntireRow.Delete xlShiftUp
    lastrow = lastrow - 1
    End If
    Next
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/728420

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice