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


"Brian Clarke" wrote in message
...
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


Steve Dunn wrote:
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" wrote in message
...
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?