Finding duplicate values

[dmcnab]

Hi all....I am working on a set of formulas that are designed to find duplicate values and I am attaching the sample workbook. I want to locate duplicate values in a row, and then use cond formatting to color the date red if there are duplicates. I am triggering the conditional formatting by using a formula that puts an "&" in an adjacent column.....that formula is an array formula but it only works if there is a value in cell d10.....if the duplicates are in, say, cells e10 and g10, then the formula doesn't seem to work.....any ideas on why? The file is at:


http://www.speedyshare.com/files/28519811/duplicates.xls


amd the password is: tedojivamuza

 

[Hui]

Select the area C4:C28

Goto Conditional Formatting and Clear Rules from the selected area


Add a new Conditional Format, based on an Equation

Use: =COUNTIF(D4:L4,D4:L4)>0

Select a Format

Apply


You can delete the equations from B4:B28 as they aren't used

 

[dmcnab]

...???...this doesn't seem to work, Hui.....even if I change it to >1, it doesn't work unless the only thing being compared are the cells in col D.....I need it to scan each row (eg: d8:l8) and if there are any duplicates in that row, then the dates in col C should change format....so, for example, if d8:l8 were 1,2,3,3-AM,4,5,3-PM,2 then the duplication of 2 would trigger a format change....but, if those cells were 1,2,3,3-AM,4,5,3-PM,6 then there would be no duplication and no format change.

 

[Hui]

Select the area C4:C28

Goto Conditional Formatting and Clear Rules from the selected area


Select the area C4:C28

Add a new Conditional Format, based on an Equation

Use: =COUNTIFS(D4:L4,D4:L4)>1

Select a Format

Apply


You can delete the equations from B4:B28 as they aren't used


refer: https://rapidshare.com/files/3234282971/Duplicates.xlsx

 

[dmcnab]

Hi Hui....thank you for the assistance....i have uploaded an updated file (duplicates3.xls)....what I am trying to do it find a formula for, say, B4 that will scan D4:L4 and look for duplicate values from the list of values in N4:n49...if it finds a duplicate, it returns a 1 in B4 (and that will trigger a format change to C4).....I have used 3 different formulas in B4, B5, B6 in an effort to find the correct one....the B4 formula (a shortened version that only looks at N4:n6) works, but I doubt that I can have 44 "OR"s nested in one formula.....any suggestions? My file is at


http://www.speedyshare.com/files/28544224/duplicates3.xls

I am also returning your file....I converted it to xls. for 2003...it doesn't work as it should...you will see that the format only changes is the duplicate value is in column D....the yellow cells show duplicate values, but they don't trigger a format change to the date...see uploaded file at:


http://www.speedyshare.com/files/28544225/hui_duplicates.xls

 

[Hui]

Please be more specific when asking questions

Your previous 2 questions don't mention comparing D4:L4 to N4:N49 at all

It only mentions finding duplicates in D4:L4


Try this

=COUNTIF(D4:L4,$N$4:$N$49)>1

 

[dmcnab]

OK...thank you for that help, Hui...and I will be more specific in the future. Thanks again

 

[Hui]

Try this as the Conditional Formatting formula

=MAX(COUNTIF(D4:L4,$N$4:$N$49))>1