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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Kit #1[/TD]
[TD="align: center"]Kit #2[/TD]
[TD="align: center"]Kit #3[/TD]
[TD="align: center"]Kit #4[/TD]
[TD="align: center"]Kit #5[/TD]
[TD="align: center"]Kit #6[/TD]
[TD="align: center"]Order[/TD]
[TD="align: center"]Kit Changes[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]


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.