Finding duplicate values

Topic

New Reply

Hi all….I have started 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 that I found here (from Hans V) 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?

Reply | Quote

Replies

Attached is an example of CountIf formulas that will check each value in the row for a duplicate. It will not check other rows.

Since you are only looking at one row the formulas are not Array formulas.

Of course you could combine the formulas into one large formula but I leave that up to you.

Hope this helps

DuthieT

Reply | Quote

Hi Duthiet….thank you for the assistance….i”m attaching 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?

Reply | Quote

In B4 enter the Array formula (confirm with ctrl-shift-enter):
=1*(SUM(ISNUMBER(MATCH(D4:L4,$N$4:$N$49,0))*1)>0)

Steve
PS: If you change you conditional format to just = B4 then you can simplify the formula to:
=SUM(ISNUMBER(MATCH(D4:L4,$N$4:$N$49,0))*1)>0

Reply | Quote

Hi Steve…I tried that but it doesn’t work…..??..??….do you want me to repost the sample with your code?

Reply | Quote

Hi Steve….I adjusted the formula in B4 to be an array formula of =IF(MAX(COUNTIF(d$4:l$4,n$4:N$49))>1,1,””) and that returns a 1 if there is a duplicate value in that row, which triggers a format change in the dates….seems to be working..thanks again for pointing me in the right direction

Reply | Quote