I'm trying to create a method that will return a value from the
differences between cells.

Example data
A B C D E F G H
Row 1: BBB BBS BSB BSS SBB SSS BBB Result
Row 2: BSB BSB BBS SSS Result

The left column is the reference and the others are compared against
this. There are at most six columns to compare against (B through
G). The cells in any column may be blank, I'd like to ignore these.
The worksheet will have over 5000 lines with data.


For row 1: Row 2
A to B: 1 difference 0
A to C: 1 2
A to D: 2 3
A to E: 1
A to F: 3
A to G: 0

So I'd like the result to be 8 for row 1 and 5 for row 2 with one
result per row.

I've thought of something like this:
=SUM(IF(MID(a1,1,1)<MID(b1,1,1),
1,0)*1,IF(MID(a1,2,1)<MID(b1,2,1),1,0)*1,IF(MID(a 1,3,1)<MID(b1,3,1),
1,0)*1)
but the resulting function for six columns would be huge. I hope
there's something more managable that can be done.

=3-((MID($A$1,1,1)=MID(B1,1,1))+(MID($A$1,2,1)=MID(B1 ,2,1))+(MID($A$1,3,1)=MID(B1,3,1)))

copy across


"Quirthanon" wrote:

I'm trying to create a method that will return a value from the
differences between cells.

Example data
A B C D E F G H
Row 1: BBB BBS BSB BSS SBB SSS BBB Result
Row 2: BSB BSB BBS SSS Result

The left column is the reference and the others are compared against
this. There are at most six columns to compare against (B through
G). The cells in any column may be blank, I'd like to ignore these.
The worksheet will have over 5000 lines with data.


For row 1: Row 2
A to B: 1 difference 0
A to C: 1 2
A to D: 2 3
A to E: 1
A to F: 3
A to G: 0

So I'd like the result to be 8 for row 1 and 5 for row 2 with one
result per row.

I've thought of something like this:
=SUM(IF(MID(a1,1,1)<MID(b1,1,1),
1,0)*1,IF(MID(a1,2,1)<MID(b1,2,1),1,0)*1,IF(MID(a 1,3,1)<MID(b1,3,1),
1,0)*1)
but the resulting function for six columns would be huge. I hope
there's something more managable that can be done.