+ Reply to Thread
Results 1 to 8 of 8

finding duplicate content within a column

  1. #1
    Registered User
    Join Date
    07-17-2006
    Posts
    10

    Question finding duplicate content within a column

    What do I need to do in order to find any duplicate content within a column with thousands of cells?

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    Here's one way

    Say data in Column A

    Enter this in Column B1 and drag down

    =IF(COUNTIF($A$1:$A$1000,A1)>1,"Duplicate","")

    VBA Noob

  3. #3
    Registered User
    Join Date
    07-17-2006
    Posts
    10
    Thanks for the reply.

    Here is what I'm trying to do: I have a list of names and would like to see if there are any duplicate people within the spreadsheet. Column D and E contain the first and last names respectively.

  4. #4
    Forum Contributor
    Join Date
    12-14-2005
    Posts
    176
    Sort your data by last name and then first name (i.e. column A then Column B). Then have your names start on the second row, so that there is no name directly above the name you are going to check.
    Then in cell C2 enter the following formula:
    =IF(AND(A2=A1,B2=B1),"Duplicate","Not Duplicate")
    The formula checks the LAST NAME and FIRST NAME of the Cell Above.
    In the example Row 2 should always be "Not Duplicate" then you know the formula is working correctly. Note cells A and B have to be exactly the same or you will get a Not Duplicate result, (i.e no extra spaces in the cells, etc.).
    Then you can do a filter for "Duplicates" and delete them.
    _______A_________B____________C
    Row 1 Last Name First Name
    Row 2 Jones______John__________"Enter your formula Here"
    Last edited by wjohnson; 09-21-2006 at 02:41 AM.

  5. #5
    Registered User
    Join Date
    09-21-2006
    Location
    Dublin
    Posts
    7
    Finding duplicate firstnames or duplicate surnames

    SURNAME
    =COUNTIF($G$2:$G$3000,"="&"*"&RIGHT(G2,LEN(G2)-FIND(" ",G2)+1))>1

    FIRSTNAME
    =COUNTIF($G$2:$G$3000,"="&LEFT(G2,FIND(" ",G2))&"*")>1

    You might be able to use those or adjust them.

    I used those in conditional formatting. Use Format / Conditional Formatting / Select "Formula is" and type in the above, then Format / Patterns / select a colour. When you have done that for one cell use the format painter to apply it to all cells in the column and you will see your duplicates in colour.
    Last edited by kincsem; 09-21-2006 at 03:31 AM.

  6. #6
    Registered User
    Join Date
    09-21-2006
    Location
    Dublin
    Posts
    7
    Quote Originally Posted by Excel_help
    Thanks for the reply.

    Here is what I'm trying to do: I have a list of names and would like to see if there are any duplicate people within the spreadsheet. Column D and E contain the first and last names respectively.
    In cell F2 type the formula = D2&E2 (this adds the firstname and lastname). Copy this formula down to the end of your name range (e.g. F2:F3000).

    Highlight cells D2 and E2.

    Select Format\Conditional Formatting from the menus.
    Condition 1 ...... Formula is ...... = COUNTIF($F$2:$F$3000,"="&$D2&$E2)>1
    Click Format \ Patterns and select a colour. Then OK; OK.

    Now use the Format painter icon (brush) and press Ctrl-Shift and down arrow to paint the format in cells D2 and E2 to the bottom of your range (e.g. D2:E3000).

    The duplicated names should be highlighted in the colour you selected.

    Good luck.

  7. #7
    Registered User
    Join Date
    04-28-2008
    Posts
    23
    what if you have your firstnames in E and surnames in F but want to check for duplicates away from the main sheet, will conditional formatting work accross different sheets.

  8. #8
    Registered User
    Join Date
    01-20-2015
    Location
    UK
    MS-Off Ver
    excel
    Posts
    1

    Re: finding duplicate content within a column

    If you need to check thousands of cells to find duplicate content then check The Plagiarism Checker tool. The Plagiarism Checker is a free online tool. It is useful for teachers, students, webmasters etc. The Plagiarism Checker helps us to find duplicate content. The Plagiarism Checker Tool shows quick result.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1