Finding identical rows in a list
in [Functions]
|
Prev: V-Lookup variation query
Next: How do I save a header?
From: Brian Clarke on 1 Jun 2010 11:52 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?
From: Dave Peterson on 1 Jun 2010 12:56 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: Brian Clarke on 2 Jun 2010 05:32 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? >
From: Steve Dunn on 2 Jun 2010 06:25 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?
From: Brian Clarke on 2 Jun 2010 06:39
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? > |