Comparing two rows

  • Thread starter Thread starter Brian Clarke
  • Start date Start date
B

Brian Clarke

I have a list of about 15,000 items, one per row. The details of each
item are in columns A to I.

I need to find those rows where ALL the entries in cols A to I are the
same as in the following row.

I have tried putting this in col J:

=IF((A893:I893)=(A894:I894),"Error","")

and I get the #VALUE! error.

Is there a way of doing this with a formula, or do I have to find
another way.

Many thanks in advance.
 
You could try this formula instead:

=SUMPRODUCT(--(A893:I893=A894:I894))

It will return a number which is the number of matching cells in
corresponding columns, so you will have a maximum of 9 if all cells
are the same. If you copy this down column J and apply Autofilter to
that column, then you will be able to select 9 from the filter drop-
down to see those duplicate rows. A variation on this would be:

=IF(SUMPRODUCT(--(A893:I893=A894:I894))=9,"duplicate","not same")

then you could just select "duplicate" in the filter drop-down.

Hope this helps.

Pete
 
Hi

You have to compare cell by cell like this:

=IF(OR(AND(A7=A8,B7=B8,C7=C8,D7=D8,E7=E8),AND(A7=A6,B7=B6,C7=C6,D7=D6,E7=E6)),"Error","")

Regards,
Per
 
Many thanks Pete, that does the job!



Pete_UK said:
You could try this formula instead:

=SUMPRODUCT(--(A893:I893=A894:I894))

It will return a number which is the number of matching cells in
corresponding columns, so you will have a maximum of 9 if all cells
are the same. If you copy this down column J and apply Autofilter to
that column, then you will be able to select 9 from the filter drop-
down to see those duplicate rows. A variation on this would be:

=IF(SUMPRODUCT(--(A893:I893=A894:I894))=9,"duplicate","not same")

then you could just select "duplicate" in the filter drop-down.

Hope this helps.

Pete
Click to expand...
 
Back
Top