Prev: V-Lookup variation query
Next: How do I save a header?
From: Dave Peterson on 2 Jun 2010 07:12
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
From: Steve Dunn on 2 Jun 2010 07:33
You're welcome Brian, the COLUMNS function just returns the number of
columns in a range.


"Brian Clarke" <bxxcfilm(a)nowhere.co.uk> wrote in message
news:u6HmV$jALHA.584(a)TK2MSFTNGP02.phx.gbl...
> 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" <bxxcfilm(a)nowhere.co.uk> wrote in message
>> news:umBRVJaALHA.4920(a)TK2MSFTNGP04.phx.gbl...
>>> 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?
>>

First  |  Prev  | 
Pages: 1 2
Prev: V-Lookup variation query
Next: How do I save a header?