Checking for Duplicates within a Workbook
 

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

Checking for Duplicates within a Workbook
 

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:

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

Checking for Duplicates within a Workbook
 

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" wrote in message
...
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

Checking for Duplicates within a Workbook
 

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" wrote:

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" wrote in message
...
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

Checking for Duplicates within a Workbook
 

Hi,

I am not quite clear about your comment. May I request you to mail me the
workbook at .

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RAYCV" wrote in message
...
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" wrote:

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" wrote in message
...
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

Checking for Duplicates within a Workbook
 

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" wrote:

Hi,

I am not quite clear about your comment. May I request you to mail me the
workbook at .

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RAYCV" wrote in message
...
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" wrote:

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" wrote in message
...
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

Checking for Duplicates within a Workbook
 

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

"RAYCV" wrote in message
...
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" wrote:

Hi,

I am not quite clear about your comment. May I request you to mail me
the
workbook at .

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RAYCV" wrote in message
...
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" wrote:

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" wrote in message
...
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

Checking for Duplicates within a Workbook
 

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


"RAYCV" wrote:

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" wrote:

Hi,

I am not quite clear about your comment. May I request you to mail me the
workbook at .

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RAYCV" wrote in message
...
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" wrote:

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" wrote in message
...
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