Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Rearrange Rows Within Columns

  1. #11
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default 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?
    Last edited by Jerry Sullivan; Mar 15th, 2019 at 03:01 PM.
    Using Excel 2016

  2. #12
    New Member
    Join Date
    Mar 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearrange Rows Within Columns

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

  3. #13
    New Member
    Join Date
    Mar 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #14
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Rearrange Rows Within Columns

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

  5. #15
    New Member
    Join Date
    Mar 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #16
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default 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. #17
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default 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"
    Last edited by Jerry Sullivan; Mar 16th, 2019 at 03:41 PM.
    Using Excel 2016

  8. #18
    New Member
    Join Date
    Mar 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearrange Rows Within Columns

    Yes, that definition works for me.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •