How to Prevent duplication of entry in row and column?

cjoly

New Member
Joined
Nov 6, 2002
Messages
22
I would like to be prevented from making twice the same entry in each row and in each column of a worksheet. I have tried Data Validation but it seems that it does not work as I am not considering a range but just the cells within a row and a column.

Thank you in advance for any suggestions?



Christophe
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Data | Validation >> Custom, Formula:

=countif($A$1:$H$80,A1)=1

A1 is the first (upper left) cell in range

Edit:

Sorry, i guess the above is not the answer to your question.
This message was edited by Raider on 2002-11-08 09:50
 
Upvote 0
I am not aware of a way to avoid duplicate entries on the fly but they can be identified by using advanced auto-filter. When you select advanced autofilter from >data>filter and select unique records only excel will not display any duplicate entries. Hope this helps
 
Upvote 0
Raider:

Thanks but you are right my problem seems to be more complex as we are not talking about a range but a row and column.

Do you know if using an RC (row column)reference could work? and if yes what it would be.
I have tried COUNTIF(RC)=1 or COUNTIF(RC,A1)=1

Yes I know I discovered computers last week.

Thanks. Christophe
This message was edited by cjoly on 2002-11-08 11:03
 
Upvote 0
Hi CJ.
Right click on your worksheet tab, choose "View Code", and paste this in as is.
See if it does what you wish. Checks the usedrange in the column and row of the changed cell.<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, Srng As Range
On Error GoTo DealWithIt
Set rng = UsedRange.Columns(Target.Column)
Set Srng = rng.Find(Target, Target, xlValues, xlWhole, , , False)
If Not Srng Is Nothing And _
Srng.Address<> Target.Address And _
Trim(Target)<> "" Then FoundDuplicate Target, Srng
Set rng = Range(Cells(Target.Row, 1), _
Cells(Target.Row, UsedRange.SpecialCells(xlCellTypeLastCell).Column))
Debug.Print rng.Address
Set Srng = rng.Find(Target, Target, xlValues, xlWhole, , , False)
If Not Srng Is Nothing And _
Srng.Address<> Target.Address And _
Trim(Target)<> "" Then FoundDuplicate Target, Srng
DealWithIt:
End Sub

Private Sub FoundDuplicate(Target As Range, Frng As Range)
Application.EnableEvents = False
If MsgBox("Duplicate entry found in " & Frng.Address & Chr(13) & _
"Would you like to clear your current entry " & _
Target & "?", vbYesNo) = vbYes Then
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True
End Sub</pre>
tom
This message was edited by TsTom on 2002-11-08 11:19
 
Upvote 0
Tom:

I don't know how you did it but you did it. This is great. It even exceeds my expectations as I can still accept a duplicate enter if I need to.

Thanks a million. The bad news is that you may hear from me more often that you would like as I am working on a small project for which now I know now that Excel is the appropriate tool. Unfortunatly, my computer skills are close to zero and for me VBA stands for Very Bad Answer.

Very best regards from Paris (France).

Christophe
 
Upvote 0
Christophe,

I'm just curious to know what cells in the row/column are you trying to validate? Also Data Validation will let you enter duplicate entries with a warning using the Error Alert tab.
 
Upvote 0
The project I am working on includes scheduling of meetings. When inputting the meetings requested, I want to be sure that I will not schedule a company twice with the same Country representative and also not schedule the company twice at the same time.
The table will look like this (see below)but of course with 20 countries, up to 100 companies and over 3 days.

Austria Belgium France Germany
9:00 AM
9:30 AM CompanyA
10:00 AM CompanyB
10:30 AM CompanyB
11:00 AM CompanyA
11:30 AM Company CCompanyB
12:00 PM
12:30 PM
1:00 PM Company C

In fact all the cells except the ones of column A and Row 1 are concerned.

Thanks for your interest.

Christophe

P.S. Sorry for the table
This message was edited by cjoly on 2002-11-08 14:21
 
Upvote 0
Christope,

You can also use Conditional Formatting to see any duplicate entries. That is to change cell or font colors if there are duplicates. I'm just providing alternative methods, as there are many in Excel.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top