What do I need to do in order to find any duplicate content within a column with thousands of cells?
What do I need to do in order to find any duplicate content within a column with thousands of cells?
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
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.
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.
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.
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).Originally Posted by Excel_help
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.
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks