Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check previous entries in column for same contents
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check previous entries in column for same contents
On Jul 30, 10:26*pm, Emily wrote:
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 In col C: =IF(COUNTIF(B:B,B1)1,"YES","NO") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check previous entries in column for same contents
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 "Emily" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check previous entries in column for same contents
On Jul 30, 10:37*pm, robzrob wrote:
On Jul 30, 10:26*pm, Emily wrote: 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 In col C: =IF(COUNTIF(B:B,B1)1,"YES","NO") ...then copy down - sorry. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to check previous entries in column for same contents
Thanks Peo and Rob! It worked!
"robzrob" wrote: On Jul 30, 10:37 pm, robzrob wrote: On Jul 30, 10:26 pm, Emily wrote: 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 In col C: =IF(COUNTIF(B:B,B1)1,"YES","NO") ...then copy down - sorry. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turning a row's contents into a column of entries | New Users to Excel | |||
How do I restrict cell entries that are determined by a previous l | Excel Discussion (Misc queries) | |||
retrieve previous entries | Excel Discussion (Misc queries) | |||
How to constrain input to previous entries | New Users to Excel | |||
Check for double entries in a row | Excel Worksheet Functions |