Thread: Rearrange Rows Within Columns Thanks:  1 Post #5243569 (1) Likes: 0

1. Re: Rearrange Rows Within Columns

Here's a clarification of what I think the OP is requesting.

As DSCfromCFA noted, the average of the totals is about 6,740, so the perfect solution would rearrange the numbers so that each row totaled 6,740.

The screenshot below shows the in the posted arrangement the first row totals 7397 which varies from 6740 by 657. The sum of all the variances is 10,209 and the maximum variance is the 4th row which totals 1912.

Excel 2016 (Windows) 32 bit
ABCDEFGH
2Variance
312345TotalFrom Target
46851,7131,4771,6371,8857,397657
51,2821,9985961,2821,7056,863123
68172,0081,7201,5731,9978,1151,375
71,1441,5262,0281,9472,0078,6521,912
85521,2991,8841,96405,6991,041
91,9012,1171,6621,64007,320580
101,0821,7031,5231,47505,783957
111,7851,9791,0711,02005,855885
121,0992,0231,2611,97706,360380
131,9571,1749522,02106,104636
141,9601,4598742,00206,295445
151,9761,6181,5332,06807,195455
161,5462,0671,48987505,977763
1710,209Sum
18Target:6,7401,912Max

Sheet1

If we exchange the positions of 2007 and 0 in column 5, the new sum of Variances from Target is 8,317 and the new Maximum variance is 1,375.
Excel 2016 (Windows) 32 bit
ABCDEFGH
23Variance
2412345TotalFrom Target
256851,7131,4771,6371,8857,397657
261,2821,9985961,2821,7056,863123
278172,0081,7201,5731,9978,1151,375
281,1441,5262,0281,94706,64595
295521,2991,8841,9642,0077,706966
301,9012,1171,6621,64007,320580
311,0821,7031,5231,47505,783957
321,7851,9791,0711,02005,855885
331,0992,0231,2611,97706,360380
341,9571,1749522,02106,104636
351,9601,4598742,00206,295445
361,9761,6181,5332,06807,195455
371,5462,0671,48987505,977763
388,317Sum
39Target:6,7401,375Max

Sheet1

The best solution can be found by brute force by trying all combinations however there are optimization approaches such a "Branch and Bound" that can greatly reduce the amount of combinations that need to be tested. https://en.wikipedia.org/wiki/Branch_and_bound

@pepsibrandon, please clarify, when you say that you want the totals to be as close as possible to each other, is the goal to minimize the Total of Variance from Target; or to minimize the value of the row with the Max variance?

2. Re: Rearrange Rows Within Columns

The goal is to minimize the value of the row with the Max variance

3. Re: Rearrange Rows Within Columns

To add some context if I had a Total Variance from target of 390 and was working with 13 rows I would rather have all 13 rows have a variance of 30 than have 11 rows have a variance of 0 and the remaining 2 rows have a variance of 195.

4. Re: Rearrange Rows Within Columns

Try this Example file, based on your data :-
https://app.box.com/s/hgqnc8z6v6p7knt1op6ko963kmypvwei

5. Re: Rearrange Rows Within Columns

MikeG thank you for the reply and the file. I ran the file and it gave me a best solution in which the difference between the maximum total and minimum is 1,417. If I manually move values around I can get these values to be much closer so I'm not sure where the disconnect is in the file and how it is choosing it's values. The table below is the solution it gave. Row 3 has the max total of 7,306. Row 8 has a total of 6,029. If I take the values for each of these rows from Column 3 and swap them row 3 total becomes 6,950 and row 8 total becomes 6,385 (just one example).

 1 2 3 4 5 Total 1144 1979 1071 875 1885 6954 552 2023 1261 1637 1705 7178 1901 1174 952 1282 1997 7306 1082 1459 874 1573 2007 6995 1785 1618 1533 1947 0 6883 1099 2067 1489 1964 0 6619 1957 1713 1477 1640 0 6787 1960 1998 596 1475 0 6029 1976 2008 1720 1020 0 6724 1546 1526 2028 1977 0 7077 685 1299 1884 2021 0 5889 1282 2117 1662 2002 0 7063 817 1703 1523 2068 0 6111

6. Re: Rearrange Rows Within Columns

Sorry it does not work for you !!!
My code just loops each column one row at a time from top to bottom, with one column at a time taking the max and min at each step.
Sound like you want the permutations of each column, combined that with the permutations of each other column. That sound like a very, very big number.

7. Re: Rearrange Rows Within Columns

Hi Mick, Yes, the brute force approach would require testing require 13! * 5! combinations which is about 740 billion trials.

I'm working on a branch and bound approach, but I'm still trying to wrap my head around a few steps to optimize that process.

Mick's example is using a definition of "totals to be as close as possible" that is slightly different than my interpretation of Brandon's clarification in Posts 12 and 13.

Mick was minimizing the difference between the highest and lowest row totals. I'm understanding the goal to be minimizing the largest difference between any single row and the average of all rows. That distinction is significant when trying to optimize an algorithm, since the latter only requires testing one row against the best candidate total.

Brandon, does this latter definition work for you?
"Minimize the largest difference between any single row and the average of all rows"

8. Re: Rearrange Rows Within Columns

Yes, that definition works for me.