SUMPRODUCT not working

[SandsB]

Someone posted this a while back and I used it and it worked. Now, I can't get it to work.

I have UserName in column A, System in column B, and Duration in column G. In column J, I want to display a 1 in each row if the combination of the value of A, B and G occurs only once in the whole file.

=IF(SUMPRODUCT(($A$2:$A2=A2)*($G$2:$G2=G2))>1,0,1)

I was trying to get a comparison of A and G to work and then add a check of C to it but I can't even get it to work for 2 columns, let alone 3. The 1 or 0 value this returns doesn't seem to have any rhyme or reason to it.

How do I compare these 3 cells in each row to check for a unique combination?
Thank you

 

[T. Valko]

Someone posted this a while back and I used it and it worked. Now, I can't get it to work.

I have UserName in column A, System in column B, and Duration in column G. In column J, I want to display a 1 in each row if the combination of the value of A, B and G occurs only once in the whole file.

=IF(SUMPRODUCT(($A$2:$A2=A2)*($G$2:$G2=G2))>1,0,1)

I was trying to get a comparison of A and G to work and then add a check of C to it but I can't even get it to work for 2 columns, let alone 3. The 1 or 0 value this returns doesn't seem to have any rhyme or reason to it.

How do I compare these 3 cells in each row to check for a unique combination?
Thank you

You need to test the entire range:

=--(SUMPRODUCT(--($A$2:$A$100=A2),--($B$2:$B$100=B2),--($G$2:$G$100=G2))>1)

 

[SandsB]

I feel stupid. I should know better.

But - this returns a 0 for every row, duplicate or not. Am I missing something else?

 

[SandsB]

wait - I was doing something else stupid. This gives me a 1 on every row that has a duplicate. What I should have said was:
I want only one of the duplicate to be flagged with a 1 or a 0. Can I do that with a sumproduct?

 

[T. Valko]

wait - I was doing something else stupid. This gives me a 1 on every row that has a duplicate. What I should have said was:
I want only one of the duplicate to be flagged with a 1 or a 0. Can I do that with a sumproduct?

Ok, then you had the right idea with your original formula

=IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),--(G$2:G2=G2))>1,1,"")

That will "mark" each successive duplicate leaving the first instance unmarked. Like this...

A..B..C
A..B..C..1
X..Y..Z
A..B..C..1
X..Y..Z..1

 

[SandsB]

I love you, man

 

[T. Valko]

I love you, man

Thanks for the feedback!