High lighting duplicate rows

I am currently cleaning up a document, my document consists of patient names and information address DOB etc... they have duplicate rows for each some contain a different address in the duplicate and some contain the same address in both records.

example: (different address)

jane doe F 11/23/1998 92 Dela Ave Somerset NJ 08873-1614
jane doe F 11/23/1998 93 Highl Ave Somerset NJ 08873-1614

john doe F 1/9/2007 911 Wood Drive North Brunswick NJ
john doe F 1/9/2007 911 Wood Road North Brunswick NJ


How can I highlight all rows with the persons with different address? or highlight the rows with patients same address.. (I dont want to DELETE) i know this function exists but I need to know which ones are being deleted.

HELP please no macros i have no idea how to use them

Hi,

Is each piece of data in its own column (Name, Gender, DOB etc...) or is it a single sting of information in the same cell?

Steve

yes it does have a header of FirstName MiddleInitial LastName Gender Birthdate SSN Address City State Zip Country Phone

Originally Posted by SteveG

Hi,

Is each piece of data in its own column (Name, Gender, DOB etc...) or is it a single sting of information in the same cell?

Steve

It is a row consisting of 12 cells and about 1700 rows

Firstly, because you're using 2003 you need a single cell that you can use in the calculation. Suppose Columns A,B,C,D are Firstname, Surname, Gender, DoB, and data starts in row 2. Add a new column (let's call it E) with the formula =UPPER(A2&B2&C2&D2), which joins those 4 bits of data into one cell. [If you want to define a duplicate using other cells, just add them in to the formula.]
Now, in column F, put =IF(COUNTIF($E$2:E2,E2)>1,"Duplicate","") and copy it down. NB YOU MUST GET THE DOLLARS IN THE RIGHT PLACE!

I am using 2007

Still works.... Would be worth updating your profile on the forum though...

I think that was auto populated I didn't put that there sorry (I'm not a forum user usually But I will be doing alot of excel so thought it would be useful to use this one)

I cant really do a sample work book as It contains sensitive information.

Im not finding the code is working that you provided... hmmm they need a way to "delete duplicates" without deleting... simply moving to a new location haa

Hi,

You could do it within Conditional Formatting itself as well using AND and COUNTIF.

=AND(COUNTIF($A$2:$A$5,$A2)>1,COUNTIF($B$2:$B$5,$B2)>1,COUNTIF($C$2:$C$5,$C2)>1,COUNTIF($D$2:$D$5,$D2)>1,COUNTIF($E$2:$E$5,$E2)>1)

I used First Name, Last Name, Gender, DOB and SSN for the criteria (columns A, B, C, D, E).

HTH
Steve

Sorry, thought I'd work in upper case, then forgot to include that in the COUNTIF.... This might solve the problem
=IF(COUNTIF($E$2:E2,UPPER(E2))>1,"Duplicate","")