Frustrated with formula!!
Posted by Barry on October 18, 2001 5:34 AM
Can anyone help? I have a worksheet that has trailer numbers in a column (D10:D145)and the delivery dates in a column (F10:F145). I need to make sure there are no duplicate entries and if there are, I need to know which cells contain the duplicates (conditional formatting). At the bottom of the list I have the following formula =IF(MAX(COUNTIF(D9:D145,F9:F145 ))>1, "DUPLICATES","NO DUPLICATES") and the conditional format I have applied to these cells is this =IF(COUNTIF(D10:D145,D11)>1,TRUE,FALSE)and then I have the text bold and the cell color in red........is there a better way to do this.........because there's something wrong with what I have and I'm starting to get a little frustrated........please help!!
Posted by Aladin Akyurek on October 18, 2001 6:57 AM
Barry,
What do you count as duplicate: same trailer and same delivery date more than once repeated?
Aladin
Posted by Barry on October 18, 2001 10:11 AM
Same trailer with same date listed more than once would be considered a duplicate. But fi trailer #1 was listed as being at a certain location on 10/15/01 and again at a different location on 10/18/01, that would be a duplicate as well.
Posted by Aladin Akyurek on October 18, 2001 11:08 AM
OK. You didn't tell where the location info is, so I'll disregard that in what follows.
Lets say taht A2:B6 houses the following sample:
{"t1",35489;"t2",35490;"t3",35491;"t1",35523;"t1",35489}
where t[N] stands for trailers, the numbers are delivery dates. Dates are here serial numbers, so don't worry about them.
Activate A2:B6.
Activate Conditional Formatting.
Select "Formula Is" for Condition 1.
Enter as formula:
=SUMPRODUCT((ISNUMBER(SEARCH(A2&B2,$A$2:$A$6&$B$2:$B$6)))+0)>1
Activate Format.
Activate either the Font or Patterns tab.
Select a color.
Click OK.
Click OK.
And (or in the stead of the above)
in C2 enter: =(SUMPRODUCT((ISNUMBER(SEARCH(A2&B2,$A$2:$A$6&$B$2:$B$6)))+0)>1)+0
Copy down as far as needed.
A 1 in C2:C6 means a duplicate, a 0 no duplicate.
Aladin
========== : Barry, : What do you count as duplicate: same trailer and same delivery date more than once repeated? : Aladin :
