Rearrange Rows Within Columns

[pepsibrandon]

[TABLE="width: 288"]
<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]1
[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[TD="width: 64, bgcolor: transparent"]5[/TD]
[TD="width: 64, bgcolor: transparent"]Total
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]685[/TD]
[TD="bgcolor: transparent"]1713[/TD]
[TD="bgcolor: transparent"]1477[/TD]
[TD="bgcolor: transparent"]1637[/TD]
[TD="bgcolor: transparent"]1885[/TD]
[TD="bgcolor: transparent"]7397[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1282[/TD]
[TD="bgcolor: transparent"]1998[/TD]
[TD="bgcolor: transparent"]596[/TD]
[TD="bgcolor: transparent"]1282[/TD]
[TD="bgcolor: transparent"]1705[/TD]
[TD="bgcolor: transparent"]6863[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]817[/TD]
[TD="bgcolor: transparent"]2008[/TD]
[TD="bgcolor: transparent"]1720[/TD]
[TD="bgcolor: transparent"]1573[/TD]
[TD="bgcolor: transparent"]1997[/TD]
[TD="bgcolor: transparent"]8115[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1144[/TD]
[TD="bgcolor: transparent"]1526[/TD]
[TD="bgcolor: transparent"]2028[/TD]
[TD="bgcolor: transparent"]1947[/TD]
[TD="bgcolor: transparent"]2007[/TD]
[TD="bgcolor: transparent"]8652[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]552[/TD]
[TD="bgcolor: transparent"]1299[/TD]
[TD="bgcolor: transparent"]1884[/TD]
[TD="bgcolor: transparent"]1964[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]5699[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1901[/TD]
[TD="bgcolor: transparent"]2117[/TD]
[TD="bgcolor: transparent"]1662[/TD]
[TD="bgcolor: transparent"]1640[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]7320[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1082[/TD]
[TD="bgcolor: transparent"]1703[/TD]
[TD="bgcolor: transparent"]1523[/TD]
[TD="bgcolor: transparent"]1475[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]5783[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1785[/TD]
[TD="bgcolor: transparent"]1979[/TD]
[TD="bgcolor: transparent"]1071[/TD]
[TD="bgcolor: transparent"]1020[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]5855[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1099[/TD]
[TD="bgcolor: transparent"]2023[/TD]
[TD="bgcolor: transparent"]1261[/TD]
[TD="bgcolor: transparent"]1977[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]6360[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1957[/TD]
[TD="bgcolor: transparent"]1174[/TD]
[TD="bgcolor: transparent"]952[/TD]
[TD="bgcolor: transparent"]2021[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]6104[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1960[/TD]
[TD="bgcolor: transparent"]1459[/TD]
[TD="bgcolor: transparent"]874[/TD]
[TD="bgcolor: transparent"]2002[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]6295[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1976[/TD]
[TD="bgcolor: transparent"]1618[/TD]
[TD="bgcolor: transparent"]1533[/TD]
[TD="bgcolor: transparent"]2068[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]7195[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1546[/TD]
[TD="bgcolor: transparent"]2067[/TD]
[TD="bgcolor: transparent"]1489[/TD]
[TD="bgcolor: transparent"]875[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]5977
[/TD]
[/TR]
</tbody>[/TABLE]

I'm working with the table above and would like to be able to rearrange the values within each column to give me the combination that has the totals as close as possible to each other. The values can move up/down within the columns but can't move from one column to another.

Thanks

 

[Panda514]

Hello,

Are you saying that you want to sort the Total column? I'm not sure what you mean by totals as close as possible to each other.

 

[pepsibrandon]

Thank you for the reply. I would like to move the values in each column up/down so that when you add the values across in each row the total of each row is as close as possible to all the other values. So example, row 4 has a total of 8,652 which is much higher than row 5, which has a total of 5,699. So I need lower values in row 4 and higher values in row 5.

 

[CyrusTheVirus]

reviewing

 

[DSCfromCFA]

Hello,

... I'm not sure what you mean by totals as close as possible to each other.

My take on what pepsib wants: to move the numbers around within their columns, so that all the totals converge as close as possible to what would be the average of the Totals (in the numbers given, that would be 6740). I would assume that each number can only be used once.

 

[pepsibrandon]

Thank you for rewording this. Your interpretation of what I'm looking for is 100% correct.

 

[DSCfromCFA]

You're welcome. Unfortunately while I understand what you want to achieve, how to achieve it is beyond my current excel skills.

 

[Jerry Sullivan]

@pepsibrandon,

Does the example you posted represent the number of columns and rows in the actual problems that you need to solve?

The amount of time it takes computers to solve this type of problem increases significantly as the data set gets larger. The example you provided is feasible to solve definitively, but if your actual problems are larger, a different approach might be needed that would provide a solution that is close to the best result, but not necessarily the best.

 

[Panda514]

Thank you for rewording it, but I still don't understand what it should look like. He wants the values in the Totals column to converge to the average of the Totals column? He said he wants the totals as close as possible to each other. Can he just sort the Totals column by increasing or decreasing?

 

[pepsibrandon]

Thanks for the reply. For this problem there will always be 13 rows and anywhere between 4 and 6 columns of data.