excel 2002 functions

[Mrs Luke]

I am trying to do a function that will look for and tell me if there is or is
not a duplicate number on a spread sheet.
EX: column B has numbers in accending order.
column F has numbers in accending order. Some of these numbers will
be duplicates of some numbers in column B but not necessarily on the same row.
I want a function in say column J that will tell me if a number in cell F1
is found in any cell in coumn B.
I tried the Logical IF function but that only seemed to work the the numbers
were on the same row. I wrote is as =+IF(B38=F36:F40,"on report","not on
report").

Can anyone help me?

 

[Fred Smith]

Use Countif to do what you want, as in:

=IF(countif(F36:F40,b38)>0,"on report","not on report").

Regards,
Fred

 

[Mrs Luke]

sorry for the errors. Accending should be ascending and I wrote is should be
I wrote it

 

[Ashish Mathur]

Hi,

You can use the following formula

IF(COUNTIF($B$1:$B$5,F1)>=1,"Duplicate","Unique")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mrs Luke]

Thanks Fred however, that only works if the number in b and f are on the same
line.
I want the function to show me that if the number in say f36 is in and cell
in column B lines 36 thru 40.
Any more suggestions. I'm at a total loss because I do not really
understand how to write functions.

 

[Mrs Luke]

Thanks for the info however, this is only working if the number is in B and F
are on the same line. Maybe I'm not explaining myself well enough.
I have a numbers in cells B36, B37, B38 such as 42, 43, 44. I have a
numbers in cells F36, F37, F38 such as 43, 44, 45. I want a formula/function
in column J that tells me if the number in cell F37 is duplicated in any
cells B36 thru B38. The result should be true. Then with if number in cell
F38 is duplicated in any cells B36 thru B38. The result should be false. So
on and so on.
Any further suggestions?

 

[Mrs Luke]

Sorry for my ignorance. I extended the formula and it works! THANKS!