PDA

View Full Version : find duplicates entries



lkmjarnold
04-04-2008, 10:09 AM
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
04-04-2008, 10:24 AM
Give this tool (http://members.iinet.net.au/~brettdj/) a try. If you need more, let us know.

georgiboy
04-04-2008, 10:35 AM
or here is some code i tried, it seemed to work
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

if its code your after :dunno

lkmjarnold
04-04-2008, 10:44 AM
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
04-04-2008, 10:53 AM
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.