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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|