find duplicates entries

[lkmjarnold]

Hello,

I would like to know if it is possible to create a macro that would compare the entries for three columns in a worksheet and then return an error message if there is a duplicate entry.

For example, I have a worksheet that includes the columns "parcel number", "book", "page" and "instrument number". I would like to know if there are duplicate entries of "parcel number & book & page" or "parcel number & instrument number". I can have duplicate entries of each of the fields individually but I do not want duplicate entries of the combination of the fields.

Thanks

[mdmackillop]

Give this tool a try. If you need more, let us know.

[georgiboy]

or here is some code i tried, it seemed to work
[VBA]Sub test_MatchCells()
Dim i As Long, cell As Range, rng1 As Range, rng2 As Range

Set rng1 = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Set rng2 = Range(Range("D1"), Range("D" & Rows.Count).End(xlUp))
On Error Resume Next
For Each cell In rng1
i = WorksheetFunction.Match(cell.Value, rng2, 0)
If Err Then
Err.Clear
Else
MsgBox "Found " & cell.Value & " In parcel No and " & cell & " in instrument No @ " & "Row:" & i
End If
Next

End Sub
[/VBA]
if its code your after

[lkmjarnold]

It will be several thousand rows. The table is linked to an access database and each row is a record in access. My columns are as follows:
Number of records, TaxAcct, MVPParcelNumber, Document Type, Book number, Page, Instrument Number, Date Recorded and Comments.
The number of records, TaxAcct, and MVPParcelNumber will have information in them but the other columns may not.

[Simon Lloyd]

Conditional formatting is definately the way to go! its quick and efficient, so, highlight your entire range, then in the white box below the word FILE on the toolbar(currently showing A1) type the range name Range1, then choose FORMAT, CONDITIONAL FORMATTING, change from Cell Is to Formula Is and then enter this =IF(countif(Range1, A1)>1,TRUE,FALSE) select your format colour and your done!, all duplicates will now show up!, i believe this to be better than code as its a worksheet function and instantaneous.