Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
does anyone know this formula
Column A B C
(999) 000-0000 (999) 111-1111 TRUE (can this check the array for duplicate and render true if no dup exists in the range (999) 111-1111 FALSE (can this check the array for duplicates and render FALSE is there is in fact a dup... any suggestion |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
does anyone know this formula
If your values are in Col A then use this in B1 and copy down
=IF(COUNTIF($A$1:$A$100,A1)1,True,False) or =IF(COUNTIF($A$1:$A$100,A1)1,"Duplicate","Unique" ) "Dylan @ UAFC" wrote: Column A B C (999) 000-0000 (999) 111-1111 TRUE (can this check the array for duplicate and render true if no dup exists in the range (999) 111-1111 FALSE (can this check the array for duplicates and render FALSE is there is in fact a dup... any suggestion |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
does anyone know this formula
know im being picky but
any way to have the true display in green and the false diplay in red, and then wold it be possible to have the word trueor false hyperlink you to the place in the sheet were the duplicate is "Sheeloo" wrote: If your values are in Col A then use this in B1 and copy down =IF(COUNTIF($A$1:$A$100,A1)1,True,False) or =IF(COUNTIF($A$1:$A$100,A1)1,"Duplicate","Unique" ) "Dylan @ UAFC" wrote: Column A B C (999) 000-0000 (999) 111-1111 TRUE (can this check the array for duplicate and render true if no dup exists in the range (999) 111-1111 FALSE (can this check the array for duplicates and render FALSE is there is in fact a dup... any suggestion |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
does anyone know this formula
You can color the True/False results by using Conditional Formatting (in the
Format menu). The hyperlink is a lot more difficult, because there can be more than one duplicate. The easiest way to display the duplicates is to Filter them (in the Data menu). Just select all the Trues to see duplicates, then select an individual phone number. Regards, Fred. "Dylan @ UAFC" wrote in message ... know im being picky but any way to have the true display in green and the false diplay in red, and then wold it be possible to have the word trueor false hyperlink you to the place in the sheet were the duplicate is "Sheeloo" wrote: If your values are in Col A then use this in B1 and copy down =IF(COUNTIF($A$1:$A$100,A1)1,True,False) or =IF(COUNTIF($A$1:$A$100,A1)1,"Duplicate","Unique" ) "Dylan @ UAFC" wrote: Column A B C (999) 000-0000 (999) 111-1111 TRUE (can this check the array for duplicate and render true if no dup exists in the range (999) 111-1111 FALSE (can this check the array for duplicates and render FALSE is there is in fact a dup... any suggestion |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
does anyone know this formula
=IF(COUNTIF($A$1:$A$1000,a1)1,FALSE,TRUE)
any way to write this where the cell does not display FALSE or TRUE until the data is actually typed "Fred Smith" wrote: You can color the True/False results by using Conditional Formatting (in the Format menu). The hyperlink is a lot more difficult, because there can be more than one duplicate. The easiest way to display the duplicates is to Filter them (in the Data menu). Just select all the Trues to see duplicates, then select an individual phone number. Regards, Fred. "Dylan @ UAFC" wrote in message ... know im being picky but any way to have the true display in green and the false diplay in red, and then wold it be possible to have the word trueor false hyperlink you to the place in the sheet were the duplicate is "Sheeloo" wrote: If your values are in Col A then use this in B1 and copy down =IF(COUNTIF($A$1:$A$100,A1)1,True,False) or =IF(COUNTIF($A$1:$A$100,A1)1,"Duplicate","Unique" ) "Dylan @ UAFC" wrote: Column A B C (999) 000-0000 (999) 111-1111 TRUE (can this check the array for duplicate and render true if no dup exists in the range (999) 111-1111 FALSE (can this check the array for duplicates and render FALSE is there is in fact a dup... any suggestion |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
does anyone know this formula
It depends on your definition of "actually typed". If you mean, until
there's something in A1, then use: =if(a1="","",IF(COUNTIF($A$1:$A$1000,a1)1,FALSE,T RUE)) Regards, Fred. "Dylan @ UAFC" wrote in message ... =IF(COUNTIF($A$1:$A$1000,a1)1,FALSE,TRUE) any way to write this where the cell does not display FALSE or TRUE until the data is actually typed "Fred Smith" wrote: You can color the True/False results by using Conditional Formatting (in the Format menu). The hyperlink is a lot more difficult, because there can be more than one duplicate. The easiest way to display the duplicates is to Filter them (in the Data menu). Just select all the Trues to see duplicates, then select an individual phone number. Regards, Fred. "Dylan @ UAFC" wrote in message ... know im being picky but any way to have the true display in green and the false diplay in red, and then wold it be possible to have the word trueor false hyperlink you to the place in the sheet were the duplicate is "Sheeloo" wrote: If your values are in Col A then use this in B1 and copy down =IF(COUNTIF($A$1:$A$100,A1)1,True,False) or =IF(COUNTIF($A$1:$A$100,A1)1,"Duplicate","Unique" ) "Dylan @ UAFC" wrote: Column A B C (999) 000-0000 (999) 111-1111 TRUE (can this check the array for duplicate and render true if no dup exists in the range (999) 111-1111 FALSE (can this check the array for duplicates and render FALSE is there is in fact a dup... any suggestion |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
does anyone know this formula
But of course the IF(..,FALSE,TRUE) is a long way round.
=if(a1="","",IF(COUNTIF($A$1:$A$1000,a1)1,FALSE,T RUE)) could be replaced by =if(a1="","",COUNTIF($A$1:$A$1000,a1)<=1) as that would similarly return FALSE or TRUE. -- David Biddulph "Fred Smith" wrote in message ... It depends on your definition of "actually typed". If you mean, until there's something in A1, then use: =if(a1="","",IF(COUNTIF($A$1:$A$1000,a1)1,FALSE,T RUE)) Regards, Fred. "Dylan @ UAFC" wrote in message ... =IF(COUNTIF($A$1:$A$1000,a1)1,FALSE,TRUE) any way to write this where the cell does not display FALSE or TRUE until the data is actually typed "Fred Smith" wrote: You can color the True/False results by using Conditional Formatting (in the Format menu). The hyperlink is a lot more difficult, because there can be more than one duplicate. The easiest way to display the duplicates is to Filter them (in the Data menu). Just select all the Trues to see duplicates, then select an individual phone number. Regards, Fred. "Dylan @ UAFC" wrote in message ... know im being picky but any way to have the true display in green and the false diplay in red, and then wold it be possible to have the word trueor false hyperlink you to the place in the sheet were the duplicate is "Sheeloo" wrote: If your values are in Col A then use this in B1 and copy down =IF(COUNTIF($A$1:$A$100,A1)1,True,False) or =IF(COUNTIF($A$1:$A$100,A1)1,"Duplicate","Unique" ) "Dylan @ UAFC" wrote: Column A B C (999) 000-0000 (999) 111-1111 TRUE (can this check the array for duplicate and render true if no dup exists in the range (999) 111-1111 FALSE (can this check the array for duplicates and render FALSE is there is in fact a dup... any suggestion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|