How to avoid same entry in numbers

Assuming your list is in column B, then in a cell in another column, enter this formula:
=IFERROR(VLOOKUP(INDEX(B,COUNTA(B)+ROW(B)-1),OFFSET(INDIRECT(ADDRESS(ROW(B),COLU MN(B))),0,0,COUNTA(B)-1),1,FALSE),"No Duplicate")

This formula alerts you if the last item in the list was already entered. Note, it only checks the last item in the list, so if the list has 10 entries and 2 and 3 are duplicates, this formula will not alert you.

I also added a Conditional Format to make it more apparent when the value of the formula cell changed.



I didn't know how many header rows you had, so this formula is slightly more complicated to account for any number of header rows. If this formula doesn't help, just post more info. I'm sure someone can help.

Robin

Another way to do it would be to have this formula in column C:

Cell C2=IF(COUNTIF($B,B2)>1,"DUPLICATE","")

Fill down to the end of the column with that formula. If the formula is in every cell in column C (not required in the header cell), it will automatically get filled into any new rows that you add later to the bottom of your table.

This will flag the new duplicate entry and will also flag the one further up in the table, in case you want to compare them.

ah yes, Badunit's solution is probably a better way to go.

Robin

It's normal, it has to scan the 299 already existing values.

Yvan KOENIG (from FRANCE samedi 30 mai 2009 16:10:53)

In that case, Robin's solution or something similar may be better. My solution has all rows checking all other rows (300x300). Robin's has one cell checking all other rows. It would be quite a bit less calculations.

My two cents:



In checker's table (two cells), cell B1 contains:
=IF(ISERROR(VLOOKUP(A,Tableau 1 :: $A,1,0)),"","DUPLICATE")

If the string typed in A1 duplicate, I know it instantaneously.
If it doesn't I just cut and paste at the end of the main list.

Yvan KOENIG (from FRANCE samedi 30 mai 2009 18:10:29)

Here is another solution. Create a footer row and put this in the footer of column B. At least that's where I'd put it.

=IF(COUNTIF(B,INDIRECT(ADDRESS(COUNTA(B)+1,2)))>1,"DUPLICATE","")

It makes the assumption that there is one header row and that the data in column B is contiguous (i.e., there are no blank cells except at the bottom of the list)

To locate the duplicate, you could have this formula in another footer cell, perhaps column C. "B14" in the formula is the location of the "duplicate detection" formula. It will return a string such as "Row 3" to indicate the location of the duplicate.

=IF(B14="DUPLICATE", "ROW "&MATCH(INDIRECT(ADDRESS(COUNTA(B)+1,2)),B,0))

I discovered I needed to edit that last formula. Between the two closing ))'s it needs a ,"" or it returns "FALSE" when there is no duplicate.

=IF(B14="DUPLICATE", "ROW "&MATCH(INDIRECT(ADDRESS(COUNTA(B)+1,2)),B,0),"")