compare excel rows

W

wdverner

Hi all,
I have an excel sheet with hundred rows. Each row has 10 colums of data.
What I want to do is compare row 1 to row 2 and count the number of
differences between the 10 columns. I then want to compare row 1 to rpw 3 and
count the difference etc.

Firstly is there a way of doing this?! If so whats the best approach?

Many thanks,
 
B

bob777

You could set up another 10x100 block to the right of the existing one
and in rows 2 to 100 put in a formula:

in k2 put

=if(a2=a$1,0,1)

then copy this down and across

just add up the ones in each row from column k across.

I am sure there are lots more ways.
 
J

Jim Rech

Assuming your data starts in A1, put this in K1 and copy down:

=SUMPRODUCT(($A$1:$J$1<>A2:J2)*1)



--
Jim
| Hi all,
| I have an excel sheet with hundred rows. Each row has 10 colums of data.
| What I want to do is compare row 1 to row 2 and count the number of
| differences between the 10 columns. I then want to compare row 1 to rpw 3
and
| count the difference etc.
|
| Firstly is there a way of doing this?! If so whats the best approach?
|
| Many thanks,
 
Top