Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate enteries
Is there a way I could querry or sort out duplicate enteries by social
security number from a large excel sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate enteries
Here are some ideas....
1) use Data- Filter- Advanced Filter. make sure to check the Unique rows option 2) Sort the SSN column. Assuming you have you SSN in Col. B cells 1 to 100, use this formula. =countif(b1,$b$1:$b$100). Any number 1 means you have a duplicate entry 3) Sort the SSN column. Assuming you have you SSN in Col. B cells 1 to 100, use this formula. put a 1 in cell A1. In A2 use this formula... = if(b1=b2,0,1). Any number = 1 means you have a unique entry. On Jun 23, 2:04*pm, boba wrote: Is there a way I could querry or sort out *duplicate enteries by social security number from a large excel sheet. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate enteries
Hi Tim
What I have is colum A1: account # B1: First Name, C: LastName D: Social Security number, Now I am trying to insert row so that I can try the formulas you gave but can't insert row. Get this error: "To prevent possible loss of data Microsoft excel can't shift nonblank cell sof the worksheet. try to delete or clear the cells to the right and below your data then select cell A1 and save your worksheet to reset the last cell used or you can move the date to a new location and try again" Columns are not even lable. A1 just starts with Account#. Trying to label (Title first by inserting row) plus to try formulas I need to insert the row? My worksheet is with 60,000 accounts. Does that matter how big the worksheet is? Thanks for your help! "Tim879" wrote: Here are some ideas.... 1) use Data- Filter- Advanced Filter. make sure to check the Unique rows option 2) Sort the SSN column. Assuming you have you SSN in Col. B cells 1 to 100, use this formula. =countif(b1,$b$1:$b$100). Any number 1 means you have a duplicate entry 3) Sort the SSN column. Assuming you have you SSN in Col. B cells 1 to 100, use this formula. put a 1 in cell A1. In A2 use this formula... = if(b1=b2,0,1). Any number = 1 means you have a unique entry. On Jun 23, 2:04 pm, boba wrote: Is there a way I could querry or sort out duplicate enteries by social security number from a large excel sheet. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate enteries
Is there a way I could querry or sort out duplicate enteries by social
security number from a large excel sheet. Here's one way. If the SSN is in column A, select column A and use: Format Conditional formatting From the pull-down choose "Formula Is" and use =COUNTIF(A:A,A1)1 Then click on "Format" and choose a "pattern" to appear in the duplicate cells. Then click OK and OK. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate enteries
It worked. Thank you very much!
"Tim879" wrote: Here are some ideas.... 1) use Data- Filter- Advanced Filter. make sure to check the Unique rows option 2) Sort the SSN column. Assuming you have you SSN in Col. B cells 1 to 100, use this formula. =countif(b1,$b$1:$b$100). Any number 1 means you have a duplicate entry 3) Sort the SSN column. Assuming you have you SSN in Col. B cells 1 to 100, use this formula. put a 1 in cell A1. In A2 use this formula... = if(b1=b2,0,1). Any number = 1 means you have a unique entry. On Jun 23, 2:04 pm, boba wrote: Is there a way I could querry or sort out duplicate enteries by social security number from a large excel sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem With Duplicate Enteries Using =INDEX(#,MATCH(#,#,#)) | Excel Discussion (Misc queries) | |||
identify duplicate enteries | Excel Worksheet Functions | |||
filtering duplicate enteries | Excel Discussion (Misc queries) | |||
filtering duplicate enteries | Excel Discussion (Misc queries) | |||
how do I find duplicate text enteries in Excel | Excel Discussion (Misc queries) |