Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate Duplicates
Hi,
I have a list of names in a column, i want to check if there are any names repeated. What would be the best way to do it? Thanks Gary |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate Duplicates
You have a few options....
I like this one. This method ignores the first occurrence of a value and displays the duplicates. With your list is in A10:A100, with A10 as the Heading: MyCol_A C1: DupeTest (or blank or any text that is NOT a data column heading) C2: =COUNTIF(A$10:A11,A11)1 (Note the dollar sign and that the formula only goes from the heading to next cell down) D1: MyCol_A (the same col heading as A1) But, only if you want the list coped to another location. Otherwise, skip that step. <Data<Filter<Advanced Filter List Range: (select $A$10:$A$100) Criteria Range: $C$1:$C$2 Click the [OK] button to filter the list in place. OR To copy the duplicates to another area: Check: Copy to another location Copy to: D1 (which contains the col heading NumVal) Check: Unique Records Click the [OK] button Any displayed values are the duplicates. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Gary" wrote: Hi, I have a list of names in a column, i want to check if there are any names repeated. What would be the best way to do it? Thanks Gary |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate Duplicates
=IF(COUNTIF($A$1:A1,A1)1,"Duplicate","")
"Gary" wrote: Hi, I have a list of names in a column, i want to check if there are any names repeated. What would be the best way to do it? Thanks Gary |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate Duplicates
Use Conditional Formatting:
Let's say your data is in column F. Set Conditional format for F1 to Formula is: =COUNTIF(F:F,F1)1 and pick a distinctive background color. Then copy the format down the column. Duplicates will then standout. -- Gary's Student "Gary" wrote: Hi, I have a list of names in a column, i want to check if there are any names repeated. What would be the best way to do it? Thanks Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: Sorting 2 columns according to matching cells, and fishing for duplicates | Excel Discussion (Misc queries) | |||
Locate duplicates in a work book by using conditional formatting | Excel Worksheet Functions | |||
Locate duplicates in a work book by using conditional formatting | Excel Worksheet Functions | |||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS | Excel Discussion (Misc queries) | |||
locate duplicates by sing conditional formatting--doesn't work for | Excel Discussion (Misc queries) |