Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find duplicates
looking for a way to check for
duplicates in a range and say column is list of unique phone numbers is there are function that would display in a corsiponding cell to let the data entry know if there is the phone number is bring duplicated don't care about highlighting anything |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find duplicates
try data validation
-- If this posting was helpful, please click on the Yes button Thank You cheers, "Dylan @ UAFC" wrote: looking for a way to check for duplicates in a range and say column is list of unique phone numbers is there are function that would display in a corsiponding cell to let the data entry know if there is the phone number is bring duplicated don't care about highlighting anything |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find duplicates
Don't know if this will solve your problem or not, but there is an easy way
to get rid of duplicate entries. Highlight the range of phone numbers including the header row, go to Filter, Advanced. There should be a cell that says to copy to another place, click that and tell it where you want to put the entries, then there is a box that says "Unique Entries Only" (or something like that). Click that and then hit OK. The new list will contain only unique entries. "Dylan @ UAFC" wrote in message ... looking for a way to check for duplicates in a range and say column is list of unique phone numbers is there are function that would display in a corsiponding cell to let the data entry know if there is the phone number is bring duplicated don't care about highlighting anything |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find duplicates
These formulas assume that your phone numbers are in A1:A10 and the
formulas are entered in B1:B10. If you want to mark only duplicated items (e.g., if 123 appears three times, only the second and third instances are marked as duplicates-- the first instance is not marked), enter the following formula in B1 and fill down to B10: =IF(COUNTIF($A$1:A1,A1)1,"duplicate","") If you want to mark all items that have duplicates (e.g,. if 123 appears three times, all three instances are marked as duplicates), enter the following formula in B1 and fill down to B10: =IF(COUNTIF($A$1:$A$10,A1)1,"duplicate","") With both formulas, duplicates are marked by the word "duplicate" in column B, or if the value in column A is not a duplicate, the cell in column B will be empty. Change the cell references to your needs. Note, though, that you must include the $ characters as shown above. If you omit the $ characters, the formulas will not work properly. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 12 Dec 2008 22:13:01 -0800, Dylan @ UAFC wrote: looking for a way to check for duplicates in a range and say column is list of unique phone numbers is there are function that would display in a corsiponding cell to let the data entry know if there is the phone number is bring duplicated don't care about highlighting anything |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find duplicates
I have a few question I am currently using =if(COUNTIF($b$1:$B$20000,b1)1,"TRUE","FALSE") When Copy down FALSE will display until it checks for the dup how can I get the cell remain blank until the date is entered. Then If possible how could we have the TRUE render in Green and the FALSE render in Red. If you are up for a chalange. If there any possible way to have the TRUE or FALSE hyperlink you to the place in the worksheet were the duplicated value exisist "Chip Pearson" wrote: These formulas assume that your phone numbers are in A1:A10 and the formulas are entered in B1:B10. If you want to mark only duplicated items (e.g., if 123 appears three times, only the second and third instances are marked as duplicates-- the first instance is not marked), enter the following formula in B1 and fill down to B10: =IF(COUNTIF($A$1:A1,A1)1,"duplicate","") If you want to mark all items that have duplicates (e.g,. if 123 appears three times, all three instances are marked as duplicates), enter the following formula in B1 and fill down to B10: =IF(COUNTIF($A$1:$A$10,A1)1,"duplicate","") With both formulas, duplicates are marked by the word "duplicate" in column B, or if the value in column A is not a duplicate, the cell in column B will be empty. Change the cell references to your needs. Note, though, that you must include the $ characters as shown above. If you omit the $ characters, the formulas will not work properly. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 12 Dec 2008 22:13:01 -0800, Dylan @ UAFC wrote: looking for a way to check for duplicates in a range and say column is list of unique phone numbers is there are function that would display in a corsiponding cell to let the data entry know if there is the phone number is bring duplicated don't care about highlighting anything |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find duplicates
The formulas I posted will display the word "duplicate" if the row is a duplicate or display nothing at all if the row is not a duplicate. I'm not sure way you changed it to "TRUE" and "FALSE" if you don't want to display those values. As an aside, you're better off not using the quoted string and use just TRUE and FALSE (no quotes). You can use Conditional Formatting (CF) from the Format menu to set conditions that when met will change the format of the cell. Select the cells to which you want to apply the formatting, and open the Conditional Formatting dialog from the Format menu. There, enter TRUE in the equal box and then click the Format button and select the font color and/or the background (pattern) color for cells that contain TRUE. Then click the Add button to add a new condition, enter FALSE in the equals box and click Format to specify the format for FALSE cells. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 13 Dec 2008 21:48:05 -0800, Dylan @ UAFC wrote: I have a few question I am currently using =if(COUNTIF($b$1:$B$20000,b1)1,"TRUE","FALSE") When Copy down FALSE will display until it checks for the dup how can I get the cell remain blank until the date is entered. Then If possible how could we have the TRUE render in Green and the FALSE render in Red. If you are up for a chalange. If there any possible way to have the TRUE or FALSE hyperlink you to the place in the worksheet were the duplicated value exisist "Chip Pearson" wrote: These formulas assume that your phone numbers are in A1:A10 and the formulas are entered in B1:B10. If you want to mark only duplicated items (e.g., if 123 appears three times, only the second and third instances are marked as duplicates-- the first instance is not marked), enter the following formula in B1 and fill down to B10: =IF(COUNTIF($A$1:A1,A1)1,"duplicate","") If you want to mark all items that have duplicates (e.g,. if 123 appears three times, all three instances are marked as duplicates), enter the following formula in B1 and fill down to B10: =IF(COUNTIF($A$1:$A$10,A1)1,"duplicate","") With both formulas, duplicates are marked by the word "duplicate" in column B, or if the value in column A is not a duplicate, the cell in column B will be empty. Change the cell references to your needs. Note, though, that you must include the $ characters as shown above. If you omit the $ characters, the formulas will not work properly. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 12 Dec 2008 22:13:01 -0800, Dylan @ UAFC wrote: looking for a way to check for duplicates in a range and say column is list of unique phone numbers is there are function that would display in a corsiponding cell to let the data entry know if there is the phone number is bring duplicated don't care about highlighting anything |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find duplicates and concatenate | Excel Worksheet Functions | |||
To find duplicates in XL sheet? | Excel Worksheet Functions | |||
Find duplicates | Excel Discussion (Misc queries) | |||
Find Duplicates | Excel Discussion (Misc queries) | |||
Find duplicates | Excel Discussion (Misc queries) |