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.
<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:
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.
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:
Code:
=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.