Conditional Formating based on Duplicate entry

Posted by Russell on December 11, 2001 5:49 PM

Trying to sort through a database of 19,000+ entries. This is a mailing list we have compiled over the last few years in several sources on several computers. So needless to say we have several similar entries, that in fact contain the same info, but due to typo's, middle intials, etc, the data is varried enough that the filter commands won't work. Also, in some cases there are husband and wife entries, father/son etc.

Is there a way to highlight cells or rows that contatin the same info? I am having problems with conditional formating. My cols. are as follows, A: First name, B:Last Name, C:Street Address - pretty simple. Any way to compare the value of A1:A2 to B1:B2 and cause one or both rows/cells to highlight. Same with cell C - if addys match, highlight that cell also. I have come very close, but I can't seem to base the values on a variable. THe only way I have made it work is to set a value that everything has to be equal to. I tried higlighting all three columns and setting the "formula is" =if(a1=a2,1,0) for condition 1, if(b1=b2,1,0) for condition 2. This got me close, but everything was getting highlighted with the actually duplicates not highlighting, and surprisingly, only columns a & c were being formated....any suggestions??

Thanks in advance.

Posted by Steven on December 11, 2001 9:51 PM

Russell,

You might want to try the macro below. I got that from somebody and should credit that person for it (can't remember his name, sorry).

Make sure you sort your list first. The macro works for column A, but you can change it in such a way that it runs it for any other column.

Goodluck

Steven

*************************

Sub HighlightDups()

'Highlights all duplicates from a list of numbers
'First SORT the list in column A, otherwise the procedure will fail

rCount = Application.CountA(ActiveSheet.Range("A:A")) 'Get row count before beginning

For LoopCount = 1 To 6 'Spin thru 6 times to get them all

For myRow = 2 To rCount 'Main loop
If Cells(myRow, 1) = Cells(myRow - 1, 1) Then 'Check for dups
Cells(myRow, 1).Select 'Select the cell
Selection.Interior.ColorIndex = 6
Qq = Qq - 1 'Change loop criteria because cell gone
End If 'End
Next 'Inside loop

Next 'Outside loop
End Sub

****************************************

Posted by rebecca on December 12, 2001 1:11 AM

Russell,

You can do step-by-step as below :

1. sort the data first
2. select the entire column which must be a Key of each row (record)
3. select "Conditional Formatting" under the Format Menu
4. select "Formula" from the Condition 1
5. type the following formula on the next field
=A1=A65536
6. click on "Format" Button to create your favour pattern
7. press OK - the duplicated record will be hightlighted.

** Means to check whether the active cell is equal to the last cell of previous row.

Posted by Steven on December 12, 2001 3:00 PM

Rebecca,

My compliments for this beautiful solution ! I'll add this one to my "excel knowledge database" !