MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 :