MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding Duplicates

Posted by Andy on October 23, 2001 12:15 AM

can any one tell me how to find if a value has been entered in a sheet already?

I need to have non adjacent columns compared - I have found the =IF(SUMPRODUCT((COUNTIF(A1:D12,A1:D12)>1)+0)>1,"Duplicates exist","") function but I can't make it work on non adjacent columns.

Sorry I'm not explaining this very well.basically I have some columns that can have duplicates in and some columns that every cell must be unique not just to that column but on the whole sheet.

Any help appreciated

Posted by Aladin Akyurek on October 23, 2001 1:05 AM


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


Posted by Andy on October 23, 2001 1:48 AM

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

Thanks though

Posted by Aladin Akyurek on October 23, 2001 2:20 AM

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.



Posted by Andy on October 23, 2001 2:42 AM


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



Posted by Aladin Akyurek on October 23, 2001 4:58 AM

> 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.


Do not apply any formatting.

Click Add.
Select Formula Is for Condition 2.


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.


PS. The workbook is underway to you.

Posted by Andy on October 23, 2001 5:30 AM

Re: Thanks


You are a God ;-)

thanks you very much