Finding identical rows in a list

[Brian Clarke]

I have a long list in columns A to I. In some cases, all the items in
adjacent rows are identical, and I need to be able to find these as
quickly as possible.

This formula identifies the number of columns in row 8 which are
identical to the corresponding items in row 7, and returns "9" when the
rows in all the 9 columns in rows 7 and 8 are identical.

=SUMPRODUCT(--(A7:I7=A8:I8))

But when I copy the formula to row 8, it does of course compare row 8
with row 9. I need the formula to compare each row with the rows
immediately above AND below. I tried this:

=SUMPRODUCT(--(A7:I7=A8:I8)OR(A7:I7=A6:I6))

but it doesn't work.

Can anyone suggest something? What am I missing here?

 

[Dave Peterson]

I think you want:

=SUMPRODUCT(--(((A7:I7=A8:I8)+(A7:I7=A6:I6))>0))

Count the number of cells in A7:i7 that match A8:i8 or A6:i6 (or both), right?

 

[Brian Clarke]

Dave,

I have tried it out, and what that does is to compare individual cells
in the row with the rows above and below. So it returns "9" if every
cell in row 7 is identical with the cell either above or below.

What I need is a function which tells me when ALL the cells in row 6 are
the same as row 7, OR all the cells in row 8 are the same as row 7.

Brian

 

[Steve Dunn]

Hi Brian

=OR(SUMPRODUCT(--(A7:I7=A6:I6))=COLUMNS(A7:I7),SUMPRODUCT(--(A7:I7=A8:I8))=COLUMNS(A7:I7))

will return TRUE if either row 6 or row 8 are identical to row 7, and FALSE
if both are different, or you could try this:

=IF(SUMPRODUCT(--(I6:Q6=I7:Q7))=COLUMNS(I7:Q7),"^","")&
IF(SUMPRODUCT(--(I7:Q7=I8:Q8))=COLUMNS(I7:Q7),"V","")

which will give visual indicators ^V pointing to which rows are identical,
kind of...

HTH
Steve D.

 

[Brian Clarke]

Hi Steve,

That seems to work. I don't remember coming across the COLUMNS function
before, I must read up on it.

Many thanks,

Brian

 

[Dave Peterson]

I'd just check twice:
=OR((SUMPRODUCT(--(A7:I7=A8:I8))=9),(SUMPRODUCT(--(A7:I7=A6:I6))=9))

 

[Steve Dunn]

You're welcome Brian, the COLUMNS function just returns the number of
columns in a range.