Duplicate entry in a column/row

[msnarayanan]

Dear all

How to restrict duplicate value in a row or column.
I want to enter numbers in a column range being a1:a100. But i have to input the numbers at random. For instance i enter 88 in a7, and 1 in a88, etc. I would like to know whether it is possible to restrict duplicates - if i enter 88 in a6 the entry should not be allowed or some warning should pop up.

I tried counting($a$1:$a$100,a1)=1

This does not work.

Your help please

m s narayanab

 

[FryGirl]

Maybe you are looking for this...

=COUNTIF($A$1:$A$100,A1)=1

 

[msnarayanan]

thanks for your assistance.
the formula works well in a single column.
but i want this formula to be copied in some other columns also.
even if i change the range in the next column, i could not enter some
numbers that are already in first column.
hope i am clear in my statement.
your help please

m s narayanan

 

[FryGirl]

Just expand the range of the formula in Data Validation.

=COUNTIF($A$1:$D$100,A1)=1

 

[msnarayanan]

Hi
I tried the formula as suggested by you; but it did work with column A only. I copied the formula to other columns also. The rows accepts. Duplicate entries also. I do not know where i went wrong.
Incidentally, duplicates should be restricted both horizontally and vertically.

Sorry for troubling you

m s narayanan

 

[FryGirl]

So we don't keep going back and forth, please add an attachment with a clear picture of before and after. What you expect and what you tried.

 

[msnarayanan]

hi
Since post reply icon did not appear i have sent a reply per email. Hope this is ok.
m s narayanan

 

[msnarayanan]

hi
further to my earlier posting, i am now able to "post reply".

i am 82 years old and am tech savvy. my passion is to learn word/excel/access/movie maker etc. i use to create varities of tables to learn more about the power of excel.

i am attaching a file containing two tables. the remarks under each of the tables are self explanatory. i tried to create a table wherein duplicate values/numbers are restricted in either rows or columns - just like "sudoku".

seeking your help and sorry for the inconvinience caused.

m s narayanan

 

[FryGirl]

Let's start off with this. Maybe it fits what you are looking for.

I've added two conditional formatting to table #2. It checks for duplicates by row and by column.

Is this meeting your intent. If so, you can just take the conditional formatting and apply it to the data validation.

 

[msnarayanan]

dear frygirls

i have entered the formula as suggested: =countif($i$6:$m$10,i6)=6
i have not entered "(" sign before =countif: is this a problem.
i am sorry to state that duplicate entries are accepted both in rows and columns
should i enter the formula in each cell.

m s narayanan

 

[FryGirl]

Highlight I6:M10

Data...Data Tools...Data Validation

Allow: Custom
Formula: =NOT(OR(COUNTIF(I$6:I$10,I6)<>1,COUNTIF($I6:$M6,I6)<>1))

 

[msnarayanan]

dear frygirl
thanks for your suggestions.
i tried with the formula entered as per instructions:
1. selected all cells i6:m10
2. selected "custom" in data validation
3. entered the formula =not(or(countif($i$6:$m$10,i6)<>1,countif($i$6:$m$6,i6)<>1 (in cell i6)

when i entered duplicate entries in the first row i.e.i6:m6 - it rejects duplicate entries. it is ok
but when i tried in the rows below i could not enter any number in any cell.
i tried many times but in vain

your assistance please. please note that this request is for academic interest.

the table is attached for your ready reference.

m s narayanan

 

[FryGirl]

Try using the formula I suggested in post #11, not this one

NOT(OR(COUNTIF($I$6:$M$10,I7)<>1,COUNTIF($I$6:$M$6,I7)<>1))

That which is highlighted in Red would prevent duplicates throughout the entire range. Again, use the formula as posted in #11. It focuses on rows and columns.

 

[msnarayanan]

Dear frygirl
I could not locate #11. But entered the following formula:
=Not(or(counting($a$1:$i$9,a1)=1))

The result is that column A and row 1
Respond to the formula while other rows/
Columns do not accept any number.
All i wanted was no duplicate entries are made in rows/columns.

Alternatively is it possible to highlight the
Duplicate entries in each row/column.

I am extremely sorry for giving trouble.

m s narayanan

 

[msnarayanan]

dear frygirl

i think you are too busy. normally you extend your help immediately.

i doubt whether i have focused my point correctly.
as a matter of clarification, i would like to state that
the numbers 1 to 5 alone should be used to fill up
25 cells (5 col * 5 rows). each number can be
entered 5 times. the 6th entry of the same
number should be restricted.
i tried the formul: =countif($a$1:$e$5,a1)=5
this is also not working.
should i opt for blank or not.
if possible, kindly help me out.
thanks
m s narayanan