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?

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 wrote:

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

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



Dave Peterson wrote:
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 wrote:
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?

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

Brian Clarke wrote:

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

Dave Peterson wrote:
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 wrote:
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

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?

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?

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?