|
Check if Duplicate with Exceptions
Hi All,
Here's a tricky formula that I can't even guess at. I need to return false or something similar to identify any duplicate value in the range C7:C799. Usually the values are numbers, but there are many blanks and a few XXXX values. The range C7:C799 is formatted as text. There are 3 exceptions that are allowed (i.e. don't return false): 1 - XXXX values can be duplicated 2 - Blanks can be duplicated 3 - If Z7:Z799 in the same row as the C column numbers both (or all) have the value 'Bank - Cheque' and date in A7:A799 is the same for both (or all) duplicate numbers, that's okay too. Dates are formatted as Date 17-Jan-10. Thanks for any help and have a great day! Michele |
Check if Duplicate with Exceptions
Hi,
Try this =if(isnumber(1*C7),if(and(countif(C$7:C799,C7)1,s umproduct($Z$7$:$Z799="Bank - Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "mjones" wrote in message ... Hi All, Here's a tricky formula that I can't even guess at. I need to return false or something similar to identify any duplicate value in the range C7:C799. Usually the values are numbers, but there are many blanks and a few XXXX values. The range C7:C799 is formatted as text. There are 3 exceptions that are allowed (i.e. don't return false): 1 - XXXX values can be duplicated 2 - Blanks can be duplicated 3 - If Z7:Z799 in the same row as the C column numbers both (or all) have the value 'Bank - Cheque' and date in A7:A799 is the same for both (or all) duplicate numbers, that's okay too. Dates are formatted as Date 17-Jan-10. Thanks for any help and have a great day! Michele |
Check if Duplicate with Exceptions
On Jan 17, 8:01*pm, "Ashish Mathur" wrote:
Hi, Try this =if(isnumber(1*C7),if(and(countif(C$7:C799,C7)1,s umproduct($Z$7$:$Z799="Bank *- Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"") -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message ... Hi All, Here's a tricky formula that I can't even guess at. *I need to return false or something similar to identify any duplicate value in the range C7:C799. Usually the values are numbers, but there are many blanks and a few XXXX values. *The range C7:C799 is formatted as text. There are 3 exceptions that are allowed (i.e. don't return false): 1 - XXXX values can be duplicated 2 - Blanks can be duplicated 3 - If Z7:Z799 in the same row as the C column numbers both (or all) have the value 'Bank - Cheque' and date in A7:A799 is the same for both (or all) duplicate numbers, that's okay too. *Dates are formatted as Date 17-Jan-10. Thanks for any help and have a great day! Michele I tried your formula in cell C1 and I'm getting an error. It suggested a correction to this: =IF(ISNUMBER(1*C7),IF(AND(COUNTIF(C$7:C799,C7)1,S UMPRODUCT($Z $7:$Z799="Bank - Cheque")*($A$7:$A799=$A7)<=1),"Duplicate",""),"") Not a biggie, just an extra $ after $A$7. But I'm now getting the famous #VALUE error. It says a value used in the formula is the wrong type. Can you suggest a place I should look? Thanks a bunch, Michele |
Check if Duplicate with Exceptions
Hi,
Are you getting the error in all cells where you copy the formula to or is it is specific cells. If it is in specific cells, then post back with entries in column C, Z and A of that/those rows -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "mjones" wrote in message ... On Jan 17, 8:01 pm, "Ashish Mathur" wrote: Hi, Try this =if(isnumber(1*C7),if(and(countif(C$7:C799,C7)1,s umproduct($Z$7$:$Z799="Bank - Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"") -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message ... Hi All, Here's a tricky formula that I can't even guess at. I need to return false or something similar to identify any duplicate value in the range C7:C799. Usually the values are numbers, but there are many blanks and a few XXXX values. The range C7:C799 is formatted as text. There are 3 exceptions that are allowed (i.e. don't return false): 1 - XXXX values can be duplicated 2 - Blanks can be duplicated 3 - If Z7:Z799 in the same row as the C column numbers both (or all) have the value 'Bank - Cheque' and date in A7:A799 is the same for both (or all) duplicate numbers, that's okay too. Dates are formatted as Date 17-Jan-10. Thanks for any help and have a great day! Michele I tried your formula in cell C1 and I'm getting an error. It suggested a correction to this: =IF(ISNUMBER(1*C7),IF(AND(COUNTIF(C$7:C799,C7)1,S UMPRODUCT($Z $7:$Z799="Bank - Cheque")*($A$7:$A799=$A7)<=1),"Duplicate",""),"") Not a biggie, just an extra $ after $A$7. But I'm now getting the famous #VALUE error. It says a value used in the formula is the wrong type. Can you suggest a place I should look? Thanks a bunch, Michele |
Check if Duplicate with Exceptions
On Jan 17, 8:35*pm, "Ashish Mathur" wrote:
Hi, Are you getting the error in all cells where you copy the formula to or is it is specific cells. *If it is in specific cells, then post back with entries in column C, Z and A of that/those rows -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message ... On Jan 17, 8:01 pm, "Ashish Mathur" wrote: Hi, Try this =if(isnumber(1*C7),if(and(countif(C$7:C799,C7)1,s umproduct($Z$7$:$Z799="Bank *- Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"") -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message .... Hi All, Here's a tricky formula that I can't even guess at. *I need to return false or something similar to identify any duplicate value in the range C7:C799. Usually the values are numbers, but there are many blanks and a few XXXX values. *The range C7:C799 is formatted as text. There are 3 exceptions that are allowed (i.e. don't return false): 1 - XXXX values can be duplicated 2 - Blanks can be duplicated 3 - If Z7:Z799 in the same row as the C column numbers both (or all) have the value 'Bank - Cheque' and date in A7:A799 is the same for both (or all) duplicate numbers, that's okay too. *Dates are formatted as Date 17-Jan-10. Thanks for any help and have a great day! Michele I tried your formula in cell C1 and I'm getting an error. *It suggested a correction to this: =IF(ISNUMBER(1*C7),IF(AND(COUNTIF(C$7:C799,C7)1,S UMPRODUCT($Z $7:$Z799="Bank - Cheque")*($A$7:$A799=$A7)<=1),"Duplicate",""),"") Not a biggie, just an extra $ after $A$7. But I'm now getting the famous #VALUE error. *It says a value used in the formula is the wrong type. *Can you suggest a place I should look? Thanks a bunch, Michele I get the value error in the cell that I put your fomula in which was C3. I think I was only to use the formula in one cell. Column A is blank. Column C has entries like blanks and these: 4412 4410 4408B 4414 4376 4420 4374C 20034 Column B (sorry dates are in B, not A so I adjusted the formula and get the same results) dates are like these: 16-Sep-09 27-Dec-09 3-Jan-10 17-Jan-10 Thanks again, Michele |
Check if Duplicate with Exceptions
Hi,
You will have to copy the formula in all the cell. Anyways. if you wish you may mail the file to me at ask(at)ashishmathur(dot)com. Please be clear about the question -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "mjones" wrote in message ... On Jan 17, 8:35 pm, "Ashish Mathur" wrote: Hi, Are you getting the error in all cells where you copy the formula to or is it is specific cells. If it is in specific cells, then post back with entries in column C, Z and A of that/those rows -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message ... On Jan 17, 8:01 pm, "Ashish Mathur" wrote: Hi, Try this =if(isnumber(1*C7),if(and(countif(C$7:C799,C7)1,s umproduct($Z$7$:$Z799="Bank - Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"") -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message ... Hi All, Here's a tricky formula that I can't even guess at. I need to return false or something similar to identify any duplicate value in the range C7:C799. Usually the values are numbers, but there are many blanks and a few XXXX values. The range C7:C799 is formatted as text. There are 3 exceptions that are allowed (i.e. don't return false): 1 - XXXX values can be duplicated 2 - Blanks can be duplicated 3 - If Z7:Z799 in the same row as the C column numbers both (or all) have the value 'Bank - Cheque' and date in A7:A799 is the same for both (or all) duplicate numbers, that's okay too. Dates are formatted as Date 17-Jan-10. Thanks for any help and have a great day! Michele I tried your formula in cell C1 and I'm getting an error. It suggested a correction to this: =IF(ISNUMBER(1*C7),IF(AND(COUNTIF(C$7:C799,C7)1,S UMPRODUCT($Z $7:$Z799="Bank - Cheque")*($A$7:$A799=$A7)<=1),"Duplicate",""),"") Not a biggie, just an extra $ after $A$7. But I'm now getting the famous #VALUE error. It says a value used in the formula is the wrong type. Can you suggest a place I should look? Thanks a bunch, Michele I get the value error in the cell that I put your fomula in which was C3. I think I was only to use the formula in one cell. Column A is blank. Column C has entries like blanks and these: 4412 4410 4408B 4414 4376 4420 4374C 20034 Column B (sorry dates are in B, not A so I adjusted the formula and get the same results) dates are like these: 16-Sep-09 27-Dec-09 3-Jan-10 17-Jan-10 Thanks again, Michele |
Check if Duplicate with Exceptions
On Jan 17, 9:11*pm, "Ashish Mathur" wrote:
Hi, You will have to copy the formula in all the cell. *Anyways. if you wish you may mail the file to me at ask(at)ashishmathur(dot)com. *Please be clear about the question -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message ... On Jan 17, 8:35 pm, "Ashish Mathur" wrote: Hi, Are you getting the error in all cells where you copy the formula to or is it is specific cells. *If it is in specific cells, then post back with entries in column C, Z and A of that/those rows -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message .... On Jan 17, 8:01 pm, "Ashish Mathur" wrote: Hi, Try this =if(isnumber(1*C7),if(and(countif(C$7:C799,C7)1,s umproduct($Z$7$:$Z799="Bank *- Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"") -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "mjones" wrote in message ... Hi All, Here's a tricky formula that I can't even guess at. *I need to return false or something similar to identify any duplicate value in the range C7:C799. Usually the values are numbers, but there are many blanks and a few XXXX values. *The range C7:C799 is formatted as text. There are 3 exceptions that are allowed (i.e. don't return false): 1 - XXXX values can be duplicated 2 - Blanks can be duplicated 3 - If Z7:Z799 in the same row as the C column numbers both (or all) have the value 'Bank - Cheque' and date in A7:A799 is the same for both (or all) duplicate numbers, that's okay too. *Dates are formatted as Date 17-Jan-10. Thanks for any help and have a great day! Michele I tried your formula in cell C1 and I'm getting an error. *It suggested a correction to this: =IF(ISNUMBER(1*C7),IF(AND(COUNTIF(C$7:C799,C7)1,S UMPRODUCT($Z $7:$Z799="Bank - Cheque")*($A$7:$A799=$A7)<=1),"Duplicate",""),"") Not a biggie, just an extra $ after $A$7. But I'm now getting the famous #VALUE error. *It says a value used in the formula is the wrong type. *Can you suggest a place I should look? Thanks a bunch, Michele I get the value error in the cell that I put your fomula in which was C3. *I think I was only to use the formula in one cell. Column A is blank. Column C has entries like blanks and these: 4412 4410 4408B 4414 4376 4420 4374C 20034 Column B (sorry dates are in B, not A so I adjusted the formula and get the same results) dates are like these: 16-Sep-09 27-Dec-09 3-Jan-10 17-Jan-10 Thanks again, Michele I will email part of the file. It is my company accounting file. Thank you. |
| All times are GMT +1. The time now is 10:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com