| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find duplicates in list
i have a single column of data. how do i highlight the duplicates in the
list. i've tried conditional formatting and can't get the formula right. some rows might be duplicated 4 times and others just 2. tia |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find duplicates in list
If you start in row 2
In cell A2 Or(A2=A1,A2=A3) "Ann" wrote: i have a single column of data. how do i highlight the duplicates in the list. i've tried conditional formatting and can't get the formula right. some rows might be duplicated 4 times and others just 2. tia |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find duplicates in list
If the duplicates aen't in adjacent rows
=COUNTIF(I:I,I1)1 "Ann" wrote: i have a single column of data. how do i highlight the duplicates in the list. i've tried conditional formatting and can't get the formula right. some rows might be duplicated 4 times and others just 2. tia |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find duplicates in list
Try these:
=IF(COUNTIF($A$1:$A$55,$A1)1,$A1,"") =IF(AND(COUNTIF($A$1:$A$55,A2)1,COUNTIF($L$1:L1,$ A2)<1),$A2,"") Note: these functions count uniques =SUMPRODUCT(1/COUNTIF(A1:A55,A1:A55)) =SUM(--(FREQUENCY(A1:A55,A1:A55)0)) =COUNT(1/FREQUENCY(A1:A55,A1:A55)) HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "joel" wrote: If the duplicates aen't in adjacent rows =COUNTIF(I:I,I1)1 "Ann" wrote: i have a single column of data. how do i highlight the duplicates in the list. i've tried conditional formatting and can't get the formula right. some rows might be duplicated 4 times and others just 2. tia |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to find and list duplicates | Excel Worksheet Functions | |||
| Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
| Find missing numbers in list, ignore duplicates | Excel Discussion (Misc queries) | |||
| How to remove duplicates from a list and copy new list to new colu | Excel Worksheet Functions | |||
| How do I find duplicates in a list | Excel Discussion (Misc queries) | |||
Linear Mode
