How to check previous entries in column for same contents

E

Emily

Hi everyone,

Column B contains a list of text data, which happens to be numbers (e.g.
1172, 4721, 6743, etc.). Every time I enter a new number in the column, I
would like another column in the same row to check the previous entries in
column B and report whether the exact same number has been entered before (if
true="yes", if false="no").

It seems simple, and reading previous posts on similar subjects has left me
totally confused. I tried an IF function but didn't get very far.

-Grateful newbie
 
R

robzrob

Hi everyone,

Column B contains a list of text data, which happens to be numbers (e.g.
1172, 4721, 6743, etc.). Every time I enter a new number in the column, I
would like another column in the same row to check the previous entries in
column B and report whether the exact same number has been entered before(if
true="yes", if false="no").

It seems simple, and reading previous posts on similar subjects has left me
totally confused. I tried an IF function but didn't get very far.

-Grateful newbie
Click to expand...

In col C: =IF(COUNTIF(B:B,B1)>1,"YES","NO")
 
P

Peo Sjoblom

Assume you enter values in column A starting in A2
in B2 put

=IF(A2="","",COUNTIF($A$2:A2,A2)>1)

copy down as far you know you will need

If you really need yes or no use

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Yes","No"))

I would be annoyed seeing all these no entries so I would use
conditional formatting to change the colour of the cell value that
has been entered more than one, select A2 and use

COUNTIF($A$2:A2,A2)>1

as conditional formatting then copy down just the formatting with the
paintbrush

or if using another column

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Entered more than once",""))






--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to check previous cells for same data and return Y/N 12
Column misalignment 0
Command Button to create a folder 0
Need to count no. of rows where cell in column A = "value1" AND cellin column B (same row) = "value2 2
How to add values in one column based on values in another column 6
Problem with formula for finding last greater than/less than or equal to value 2
How do I filter data by more than one column in excel 0
unique entry sum 0

Top