Check if Duplicate with Exceptions

[mjones]

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

 

[Ashish Mathur]

Hi,

Try this

=if(isnumber(1*C7),if(and(countif(C$7:C799,C7)>1,sumproduct($Z$7$:$Z799="Bank
- Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[mjones]

Hi,

Try this

=if(isnumber(1*C7),if(and(countif(C$7:C799,C7)>1,sumproduct($Z$7$:$Z799="Bank
 - Cheque")*($A$7$:$A799=$A7)<=1),"Duplicate",""),"")

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

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,SUMPRODUCT($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

 

[Ashish Mathur]

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]

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

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

 

[Ashish Mathur]

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]

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

I will email part of the file. It is my company accounting file.
Thank you.