Use SUMPRODUCT to find duplicate rows (not one column)

  • Thread starter Thread starter djb015
  • Start date Start date
D

djb015

I want to use SUMPRODUCT to highlight where I have duplicate information
in rows. I realise that I can find duplicates in other ways but I want
to get this approach working

This formula works fine if I have three columns of data in A to C and
my first data is in A2:C2

D2 =SUM(SUMPRODUCT((A2=$A$2:A2)*(B2=$B$2:B2)*(C2=$C$2:C2)*1))

so if I copy this down this flags each duplicate row and gives it a
duplicate number

But it will be tiresome to keep on adding elements. So i tried
this
D2 =SUMPRODUCT((A2:C2=$A$2:C2)*1)/3

Thinking that if it worked I could change the C to a Z etc and quickly
long at 26 Column comparisons

The problem with this is that it evaluates any individual comparisons
that match in any row and sums if any are true whereas I essentialy
want a AND like operator to check whether there are three TRUE's per
row and count them as 1.

When I looked at the array like result of the SUMPRODUCT I did notice
that there was a ";" marker showing the end of a Row, ie

TRUE,TRUE,TRUE;TRUE,FALSE,TRUE

I thought this may give a lead?

So my question is can I somehow tweak this formula to look a range of
columns in a row rather than column by column and flag duplicates?

Thank you
 
Back
Top