| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2002 functions
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? -- mrs luke |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel 2002 functions
Use Countif to do what you want, as in:
=IF(countif(F36:F40,b38)0,"on report","not on report"). Regards, Fred "Mrs Luke" wrote in message ... 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? -- mrs luke |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel 2002 functions
sorry for the errors. Accending should be ascending and I wrote is should be
I wrote it -- mrs luke "Mrs Luke" wrote: 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? -- mrs luke |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel 2002 functions
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" wrote in message ... 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? -- mrs luke |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel 2002 functions
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 "Fred Smith" wrote: Use Countif to do what you want, as in: =IF(countif(F36:F40,b38)0,"on report","not on report"). Regards, Fred "Mrs Luke" wrote in message ... 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? -- mrs luke |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel 2002 functions
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 "Ashish Mathur" wrote: 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" wrote in message ... 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? -- mrs luke |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel 2002 functions
Sorry for my ignorance. I extended the formula and it works! THANKS!
-- mrs luke "Ashish Mathur" wrote: 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" wrote in message ... 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? -- mrs luke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Outlook 2002 calendar dates exported to Excel 2002 sort incorrectl | Excel Worksheet Functions | |||
| Changes in date functions between excel 2002 and 2003 | Excel Worksheet Functions | |||
| nested if functions in Excel 2002 | Excel Worksheet Functions | |||
| VBA Functions Excel 2002 | New Users to Excel | |||
| Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) | |||
Linear Mode
