Alerts to duplicate entries in Excel

  Pusherman 21:41 05 Jun 2003

Consider a simple spreadsheet with two columns; Postcode and door number (column A contains postcode and column B contains door number).

In Excel is there a way that the user can be alerted to the fact that the same postcode and door number has already been entered. For example;

1) ST4 4XE 22

2) ST8 2FG 99

3) ST4 4XE 88

4) ST4 4XE 22

The postcode is entered into cell A4 but when the door number is entered into cell B4 the user will be notified that the same postcode and door number
has already been entered into A1 and B1. No report will be given for the third line because the door number is different.

Thanks again everyone


  VoG™ 21:48 05 Jun 2003

You could use ASAPUtilities from click here to highlight duplicate entries.

However, I think that you really want a macro that examines the data as it is entered.

What exactly do you want to happen when a duplicate is entered? (N.B. This may or may not be possible!)

  Pusherman 21:57 05 Jun 2003

When a duplicate entry is entered an on screen prompt saying something to the effect that the postcode and door number have already been entered. I will, however, checkout ASAPUtilities. Thanks for your reply, any more suggestions...

  VoG™ 22:04 05 Jun 2003

Right click the worksheet tab and select view code. Copy and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim nRow As Integer, iRow As Integer, Pcode As String

nRow = Target.Row

Pcode = Target.Value

For iRow = 1 To nRow - 1

If ActiveSheet.Range("A" & iRow) = Pcode Then

MsgBox ("This is a duplicate")

End If

Next iRow

End Sub

ALT+F11 to exit the Visual Basic Editor.

This is imperfect in that ir=t assumes that the postcode is entered in Column A and it only checks for duplicates in that column.

You should enjoy ASAPUtilities anyway.

If this does not do what you want, please post back.

  Pusherman 22:15 05 Jun 2003

There is nothing to stop me from entering the postcode and the door number in the same cell (I'm the only one who will be using the spreadsheet anyway) so your solution will work fine.

Cheers, Pusherman.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?