Finding Duplicates

Andy,

You don't need to do it all with a single formula. Create another for other columns.

Aladin

I don't think i understand, won't that only check that column, I need to check against numerous columns

Thanks though

Andy --

I seem not to understand your problem for the following reason: You want the formula to work across non-adjacent columns. That means you want to check (a) every value or (b) some target value whether or not it's duplicated somewhere in the worksheet.

If (a) is the case, I don't see how you can carry that out: you'll need as many cells as the cells that you want to check. And if you have lots of cells in your worksheet, you'll have a performance problem with the SUMPRODUCT formula.

If (b) is the case, you might apply the formula selectively. I was hoping this could be your case.

I'd like to see your worksheet if that's possible.

Regards,

Aladin

Aladin

I have emailed you an examle of what i'm tring do any help would be good

Thanks

Andy

> I have emailed you an examle of what i'm tring do any help would be good

Andy --

I gather that you just want to assess whether a given set of non-adjacent ranges contain duplicated values where no duplicates allowed.

Although a single SUMPRODUCT formula such as

=IF(SUMPRODUCT((COUNTIF(A1:D12,A1:D12)>1)+0)>1,"Duplicates exist","")

is capable of doing that, it cannot be applied to non-adjacent ranges. For example, if C1:C12 is allowed to contain dups, the above formula would give a false alarm.

I'd propose using conditional formatting for the task at hand.

You have in B1:K5 the following sample data (with zeroes standing for empty cells)

{"kac 001","kac 012","n/a","n/a","kap 001","kap 012","n/a","n/a","kac 0011","kac 0112";"daf 001","daf 012","n/a","n/a","kap 001","kap 012","n/a","n/a","daf 0011","daf 0112";"baf 001","baf 012","n/a","n/a",0,0,"n/a","n/a","baf 0101","baf1 012";"fff 001","fff 150",0,0,0,0,0,0,"fff 1001","fff 1150";0,0,0,0,0,0,0,0,"kap 001","x"}

where cells containing n/a indicate the ranges that must be excluded from assessment for duplicates.

In A2 enter: =COLUMN(D:D) [ or just 4 ]
In A3 enter: =COLUMN(E:E) [ or just 5 ]
In A4 enter: =COLUMN(H:H) [ or just 8 ]
In A5 enter: =COLUMN(I:I) [ or just 9 ]

These are the columns to exclude.

Select B1:K5.
Activate Format|Conditional Formatting.
Select Formula Is for Condition 1.
Enter

=ISNUMBER(MATCH(COLUMN(B1),$A$2:$A$5,0))

Do not apply any formatting.

Click Add.
Select Formula Is for Condition 2.
Enter

=(COUNTIF($B$1:$K$5,B1)>1)*(LEN(B1)>0)

Activate Format.
Select e.g., red as color on the Patterns tab.
Click OK.
Click OK.

The foregoing will make only those cells red where no duplicates are allowed.

Aladin

PS. The workbook is underway to you.

Re: Thanks

Aladin

You are a God ;-)

thanks you very much