Rearrange Rows Within Columns

[pepsibrandon]

1	2	3	4	5	Total
685	1713	1477	1637	1885	7397
1282	1998	596	1282	1705	6863
817	2008	1720	1573	1997	8115
1144	1526	2028	1947	2007	8652
552	1299	1884	1964	0	5699
1901	2117	1662	1640	0	7320
1082	1703	1523	1475	0	5783
1785	1979	1071	1020	0	5855
1099	2023	1261	1977	0	6360
1957	1174	952	2021	0	6104
1960	1459	874	2002	0	6295
1976	1618	1533	2068	0	7195
1546	2067	1489	875	0	5977

<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody> </tbody>

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.