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.

Duplicate rows

Discussion in 'Business Applications' started by Toddles18, Sep 15, 2004.

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

    Toddles18 Thread Starter

    Joined:
    Jul 18, 2001
    Messages:
    2,910
    I have a spreadsheet with around 3000 rows. There are some duplicates. Is there an easy way to find the duplicates in excel and export them to another sheet for review, or even highlight or separate them in some manner?
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    do you have access you could import to access and remove duplicates. I did this for large data sents - infact linked input and then export as spreadsheet.

    is this the entire row or just 1 cell in each row which is duplicated

    i do a sort and then OR((A1=A2),(A2=A3)) then you can see if A1=A2=A3 and see duplicates.

    or

    if the rows all the same - depending on number of characters
    you could concatenate them into one cell and do the above.
    A1&A2&A3
    then use OR test after sort

    i'm sure theres better ways with macros - just my twopence worth
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    How many columns are you using for each record? Be more specific on what comprises a duplicate record and we'll help. Does each duplicate record only use a single column or does a duplicate record mean that the entries in columns A, B, C, etc. are all the same for each record?




    Rollin
     
  4. Toddles18

    Toddles18 Thread Starter

    Joined:
    Jul 18, 2001
    Messages:
    2,910
    There are 8 columns. Column A is the key value that dictates whether it is a duplicate or not.
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    can you zip up and attached - and then describe as "rollin' rog" suggests which columns = duplicates
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Select the entire range of cells in Column A and click INSERT --> NAME --> DEFINE and give the range selection the name Range1

    Next click FORMAT --> CONDITONAL FORMAT and change the Condition1 drop down selection to read "FORMULA IS"
    (make sure your range of cells in column A is still selected)

    Paste the formula below into the formula textbox then click the format button and select the highlight color for your duplicate records. Now click OK and all duplicate items in column A will be highlighted.

    =IF(COUNTIF(Range1, A1)>1,TRUE,FALSE)

    (Change A1 in the formula above to represent the first cell in Column A containing data)



    Rollin
     
  7. 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/274271

  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