Formula Help: Need to combine two CountIF's within a IF/AND Statement

[Johnny Thunder]

Hi,

I am working with a formula that I am getting some weird results and hopin someone has a solution.

I have two Concatenate formulas that combine about 3 Columns of data, These formulas are in Col J:K.

I then have two other columns that individually look at each concatenate formula nand determine if the value row by row is unique or a duplicate using this: =IF(A3=15660010,IF(COUNTIF(J:J,J3)>1,"Duplicate","Unique"),"") and =IF(A3=15660010,IF(COUNTIF(K:K,K3)>1,"Duplicate","Unique"),"")

What I was hoping to do is delete both columns with the CountiF statement and just have a single column that does a look up to the Concatenate columns at the same time and says "If(AND( Both Columns have a unique value then flag as unique, if not flag as "Duplicate"

The basis of the formula seemed pretty straight forward so I wrote it but I am noticing that if Column J is a "Duplicate" and Column K is a "Unique" the formula returns "Unique" which is wrong, both would need to be unique values for the formula to return "Unique"

Any ideas of the issue?

Here is the formula: =IF(A3=15660010,IF(AND(COUNTIF(J:J,J3)>1,COUNTIF(K:K,K3)>1),"Duplicate","Unique"),"")

 

[snjpverma]

Try this:

=IF(A3=15660010,IF(AND(COUNTIF(J:K,J3)>1),"Duplicate","Unique"),"")

 

[Peter_SSs]

Here is the formula: =IF(A3=15660010,IF(AND(COUNTIF(J:J,J3)>1,COUNTIF(K:K,K3)>1),"Duplicate","Unique"),"")

You need OR instead of AND

=IF(A3=15660010,IF(OR(COUNTIF(J:J,J3)>1,COUNTIF(K:K,K3)>1),"Duplicate","Unique"),"")

BTW, you should also amend your signature as those old HTML Makers do not work in the new forum software. See my signature for the similar alternative.

 

[Johnny Thunder]

Thanks for the help on the formula and the suggestion for the signature. I have updated. Thanks!

 

[Peter_SSs]

You're welcome.

 

[Johnny Thunder]

You need OR instead of AND

=IF(A3=15660010,IF(OR(COUNTIF(J:J,J3)>1,COUNTIF(K:K,K3)>1),"Duplicate","Unique"),"")

BTW, you should also amend your signature as those old HTML Makers do not work in the new forum software. See my signature for the similar alternative.

Hi Peter, so when I tried the proposed solution with the change to "OR" I thought everything would be fine but I am encountering an issue where the formula is resulting in "Duplicate" even though the second statement on its own yields a "Unique" value?

I manually created formulas that were specific to 1 column and the first statement looking at Column J results in a "Duplicate" for a specific row, and the 2nd statement returns a "Unique" looking at the data in Column K but for some unknown reason it seems like the IF/OR statement is only looking at the 1st result since the formulas is returning a "Duplicate" as my result when it should be a "Unique"

 

[Johnny Thunder]

Here is the data that I was looking at,

Book1
	J	K	L	M
2	Concatenate 1st Qtr	Concatenate 2nd Qtr	Compare	Manual Formula (Col K)
77	15660010_VFAKE05601_145833	15660010_VFAKE05601_145833	Duplicate
78	15660010_VFAKE05601_0	15660010_VFAKE05601_145833	Duplicate
79	15660010_VFAKE05601_0	15660010_VFAKE05601_83333	Duplicate	Unique
Custom FBL3N

Cell Formulas
Range	Formula
J77:J78, J79	J77	=A77&"_"&B77&"_"&ABS(H77)
K77:K78, K79	K77	=A77&"_"&B77&"_"&ABS(I77)
L77:L78, L79	L77	=IF(A77=15660010,IF(OR(COUNTIF(J:J,J77)>1,COUNTIF(K:K,K77)>1),"Duplicate","Unique"),"")
M79	M79	=IF(A79=15660010,IF(COUNTIF(K:K,K79)>1,"Duplicate","Unique"),"")

 

[edvrdsdvskj]

I guess maybe issue is with cell absolute/relative references

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	#1	#2	#3	#4	#5	#6	#7	#8	#9	Concatenate 1st Qtr	Concatenate 2nd Qtr	Compare	Manual Formula (Col K)
2	15660010	VFAKE05601						145833	145833	15660010_VFAKE05601_145833	15660010_VFAKE05601_145833	Unique
3	15660010	VFAKE05601						145833	145833	15660010_VFAKE05601_145833	15660010_VFAKE05601_145833	Duplicate
4	15660010	VFAKE05601						0	145833	15660010_VFAKE05601_0	15660010_VFAKE05601_145833	Duplicate
5	15660010	VFAKE05602						0	83333	15660010_VFAKE05602_0	15660010_VFAKE05602_83333	Unique	Unique
Sheet4

Cell Formulas
Range	Formula
J2:J4, J5	J2	=A2&"_"&B2&"_"&ABS(H2)
K2:K4, K5	K2	=A2&"_"&B2&"_"&ABS(I2)
L2:L4, L5	L2	=IF(A2=15660010,IF(OR(COUNTIF($J$2:J2,J2)>1,COUNTIF($K$2:K2,K2)>1),"Duplicate","Unique"),"")
M5	M5	=IF(A5=15660010,IF(COUNTIF(K:K,K5)>1,"Duplicate","Unique"),"")

 

[Johnny Thunder]

Thank you for looking at this. I will give that a try and see if I run into any other issues.

 

[Peter_SSs]

Here is the data that I was looking at,

Earlier you said ..

both would need to be unique values for the formula to return "Unique"

In post 7 cells J78 and J79 appear to be the same & therefore do not meet your earlier statement to be Unique.