A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Finding identical rows in a list



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 04:52 PM posted to microsoft.public.excel.worksheet.functions
Brian Clarke[_2_]
external usenet poster
 
Posts: 5
Default Finding identical rows in a list

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?
  #2  
Old June 1st, 2010, 05:56 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Finding identical rows in a list

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
  #3  
Old June 2nd, 2010, 10:32 AM posted to microsoft.public.excel.worksheet.functions
Brian Clarke[_2_]
external usenet poster
 
Posts: 5
Default Finding identical rows in a list

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?


  #4  
Old June 2nd, 2010, 11:25 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Finding identical rows in a list

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?


  #5  
Old June 2nd, 2010, 11:39 AM posted to microsoft.public.excel.worksheet.functions
Brian Clarke[_2_]
external usenet poster
 
Posts: 5
Default Finding identical rows in a list

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?


  #6  
Old June 2nd, 2010, 12:12 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Finding identical rows in a list

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
  #7  
Old June 2nd, 2010, 12:33 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Finding identical rows in a list

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?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.