Calculating Number of Changes between 2 Ranges

[Tlazenby]

Hello,

I am attempting to count the number of differences between ranges and then optimize the order in which these ranges should be ordered to minimize the changes.

Kit #1	Kit #2	Kit #3	Kit #4	Kit #5	Kit #6	Order	Kit Changes
1	1	1	1	5	6	7	4
1	1	2	2	7	9	6	2
1	1	2	6	6	9	5	3
2	2	3	5	6	9	4	1
2	2	5	6	7	9	3	1
2	2	6	7	7	9	2	2
3	3	6	7	7	9	1	0

<tbody>
</tbody>

I have devised a way to complete this currently but am looking for a more simple method so I can combine this with other portions of my workbook. The formula I am currently using is as follows:

=IFERROR(IF(I2=1,0,IF((COUNTIF(INDIRECT(K2),C2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),C2))<=-1,0,(COUNTIF(INDIRECT(K2),C2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),C2)))+IF(D2=C2,0,IF((COUNTIF(INDIRECT(K2),D2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),D2))<=-1,0,(COUNTIF(INDIRECT(K2),D2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),D2))))+IF(OR(E2=D2,E2=C2),0,IF((COUNTIF(INDIRECT(K2),E2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),E2))<=-1,0,(COUNTIF(INDIRECT(K2),E2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),E2))))+IF(OR(F2=E2,F2=D2,F2=C2),0,IF((COUNTIF(INDIRECT(K2),F2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),F2))<=-1,0,(COUNTIF(INDIRECT(K2),F2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),F2))))+IF(OR(G2=F2,G2=E2,G2=D2,G2=C2),0,IF((COUNTIF(INDIRECT(K2),G2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),G2))<=-1,0,(COUNTIF(INDIRECT(K2),G2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),G2))))+IF(OR(H2=G2,H2=F2,H2=E2,H2=D2,H2=C2),0,IF((COUNTIF(INDIRECT(K2),H2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),H2))<=-1,0,(COUNTIF(INDIRECT(K2),H2)-COUNTIF(INDIRECT(INDEX($I$2:$K$25,MATCH(I2-1,$I$2:$I$25,0),3)),H2))))),"")

I certainly do not expect anyone to read through that formula just providing the method I currently am using.

I then use Excel solver to optimize the order, unfortunately my current method requires the use of the Evolutionary method of solving whereas I would prefer to be able to make this linear so I could add the changes as determent in a larger model.

Can email my workbook if it would be beneficial to anyone willing to help out.