Unique entries

[Macker72]

Hi
I am using excel to record licence numbers and i want to ensure that i don't enter the same licences keys twice. i know i can sort each time and search for duplicates but this will not be practical.
thanks for your help.
Macker

[Aladin Akyurek]

On 2002-04-18 02:02, Macker72 wrote:
Hi
I am using excel to record licence numbers and i want to ensure that i don't enter the same licences keys twice. i know i can sort each time and search for duplicates but this will not be practical.
thanks for your help.
Macker

Lets suppose that the licence keys are entered in A from A2 on.

Activate A2 (which is as yet empty).
Activate the option Data|Validation.
Select Custom for Allow.
Enter as formula:

=COUNTIF($A$1:A1,A2)=0

Activate OK.

Copy down A2 as far as needed.

[Ian Mac]

On 2002-04-18 02:02, Macker72 wrote:
Hi
I am using excel to record licence numbers and i want to ensure that i don't enter the same licences keys twice. i know i can sort each time and search for duplicates but this will not be practical.
thanks for your help.
Macker

If your records are in Column A.

Goto insert>name>define, type a name that mean something, like RecordRange.

in the Refers To box type

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

press add

while in the Define Names box create a name like UniqueRecords

the Refers to box put:

=COUNTA(Sheet1!$A:$A)-IF(LEN(RecordRange)>0,SUMPRODUCT(1/COUNTIF(RecordRange,RecordRange)))

press add.

Goto Data>Validation>Custom and the in Formula box type

=UniqueRecords=0

should do the trick.

[nisht]

Try with this example workbook..


download file nos.27 ("uniqueentries")


http://www.pexcel.com/download.htm

you can see the code.

nishith desai
http://www.pexcel.com