Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find duplicate occurances of three cells in rows?
I am trying to identify rows in a worksheet that have duplicate values in a
combination of three cells that should be unique in the worksheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find duplicate occurances of three cells in rows?
Say cells A1, B1, C1 shuold all be unique.
Set the conditional format of A1 to hi-light if A1 equals B1 Set the conditional format of B1 to hi-light if B1 equals C1 Set the conditional format of C1 to hi-light if C1 equals A1 So if any of the three are equal, some hi-lighting will appear copy the formats down the columns. -- Gary''s Student gsnu200704 "TnT Tech Man" wrote: I am trying to identify rows in a worksheet that have duplicate values in a combination of three cells that should be unique in the worksheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find duplicate occurances of three cells in rows?
Another thought ..
Assuming key data in cols A to C within rows 2 to 100 with rows containing any blank cells within cols A to C to be disregarded Place in D2: =IF(COUNTBLANK(A2:C2)<0,"",IF(SUMPRODUCT(($A$2:$A $100=A2)*($B$2:$B$100=B2)*($C$2:$C$100=C2))1,"X", "")) Copy D2 down to D100. Col D will flag "X" for all lines with duplicates, inclusive of the 1st occurrences. You could then easily apply autofilter on D1 to filter out all these cases. If you want to flag only the duplicate lines (ie leaving out the 1st occurrences), use instead in D2: =IF(COUNTBLANK(A2:C2)<0,"",IF(SUMPRODUCT(($A$2:A2 =A2)*($B$2:B2=B2)*($C$2:C2=C2))1,"X","")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TnT Tech Man" wrote: I am trying to identify rows in a worksheet that have duplicate values in a combination of three cells that should be unique in the worksheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find cells with non-conditional formatting? | New Users to Excel | |||
find multiple occurances of a value and match to values in another | Excel Discussion (Misc queries) | |||
How to count blank cells (Rows) between Non-Blank cells in Col "A" | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) |