| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DATE | Excel Worksheet Functions | |||
| Make list of cells in array and checking for duplicates | Excel Worksheet Functions | |||
| checking for duplicates | Excel Worksheet Functions | |||
| Error Checking for Duplicates in List | Excel Worksheet Functions | |||
| Checking for duplicates - think this is simple | Excel Discussion (Misc queries) | |||
Linear Mode
