Checking for Duplicates within a Workbook

[RAYCV]

Hi, I am using the following array formula to check for duplicates within a
worksheet.

{=IF(MAX(COUNTIF('Daniel S'!A4:A1000,A4:A1000))>1,"Duplicate","No
Duplicates")}

Is there a way that I can extend this so that it checks the same range
across 10 worksheets within a workbook.

Thanks

 

[Stefi]

Try to extend your formula this way:

{=IF(AND(MAX(COUNTIF('Daniel S'!A4:A1000,A4:A1000))>1,MAX(COUNTIF('Next
sheet'!A4:A1000,A4:A1000))>1, ... ),"Duplicate","No
Duplicates")}

Regards,
Stefi

„RAYCV†ezt írta:

 

[Ashish Mathur]

Hi,

Unfortunately, Excel cannot lookup in a thee dimensional array. What you
can do here is use the Data > Consolidate Function. In a separate sheet,
while you are in cell A1, go to Data > Consolidate and in the reference box,
give the range in sheet 1 and click on Add. Repeat this process of adding
the range from the nine other worksheets as well. After all the 10
worksheets are added, check the box for 'Create Links to Source Data" and in
the function drop down, select "Count". Once you click on OK, (from all the
sheets) will come in this sheet. Now you can use your function.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[RAYCV]

Hi Thanks for this. However it always show that there are duplicates as the
consolidation displays zeros??

Anyway to omit the zeros in the formula?

 

[Ashish Mathur]

Hi,

I am not quite clear about your comment. May I request you to mail me the
workbook at (e-mail address removed).

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[RAYCV]

Because the Sum function is being used in the Consolidated worksheet, when it
find a record number eg 1234, the total for that is 1234. Therefore, Excel
sees the original record and the total as a duplicate. Hope this makes
sense??

 

[Ashish Mathur]

Hi,

Disregard the result of the sum or the count function. Once you have all
the figures in one place, you can use any formula to know the duplicates

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Lori]

You could use variance as the consolidation by doing edit replace "sum" by
"var" and then look for non zero values. Another option is to use a 3d
formula:

=MAX(FREQUENCY(Sheet1:Sheet10!A:A,Sheet1:Sheet10!A:A))