Duplicate enteries

[boba]

Is there a way I could querry or sort out duplicate enteries by social
security number from a large excel sheet.

 

[Tim879]

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.

 

[boba]

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!

 

[MyVeryOwnSelf]

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.

 

[boba]

It worked. Thank you very much!

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.