B
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?
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?