Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Rearrange Rows Within Columns

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

    Default Rearrange Rows Within Columns

    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

    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

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

    Default Re: Rearrange Rows Within Columns

    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.

  3. #3
    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

    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.

  4. #4
    Board Regular CyrusTheVirus's Avatar
    Join Date
    Jan 2015
    Location
    Glens Falls, USA
    Posts
    732
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rearrange Rows Within Columns

    reviewing
    Last edited by CyrusTheVirus; Mar 13th, 2019 at 11:34 PM.

  5. #5
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rearrange Rows Within Columns

    Quote Originally Posted by Panda514 View Post
    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.
    Last edited by DSCfromCFA; Mar 14th, 2019 at 12:35 AM. Reason: removed assumed gender

  6. #6
    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

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

  7. #7
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rearrange Rows Within Columns

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

  8. #8
    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

    @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.
    Last edited by Jerry Sullivan; Mar 15th, 2019 at 12:13 PM.
    Using Excel 2016

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

    Default Re: Rearrange Rows Within Columns

    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?

  10. #10
    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

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

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
  •