+ Reply to Thread
Results 1 to 11 of 11

Macro to sort column datasets based on totals

  1. #1
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Macro to sort column datasets based on totals

    Hey Guys & Girls,

    I'm quite sure this is possible but I think it can only be done by macro, if you know a fast manual way of doing it then I don't mind if it is manual.

    I have attached an example workbook with two sheets "Before" and "After" to make it clear of the outcome.

    If you look in "Before" you will see that each dataset is filled with color to make it easier, a dataset is 3 columns wide (D:F, G:I, J:L...etc)

    I need it to add (sum) all of the numbers in the first two columns of each dataset and then sort the groups of datasets and shuffle them around in their correct groups. (See the "After" sheet for the outcome (please note I do NOT need the forumla what is in Row 2, that is just for show.

    My main document travels down 5,000+ rows and spans across A LONG way past AAA

    Any help would be appreciated.
    - Hyflex
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Macro to sort column datasets based on totals

    see your file attached called "hyflex.xlsm". I copied sheet "before" to a new sheet called "sheet1".
    the macro is operated on sheet1.
    now try this macro

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by venkat1926; 10-06-2011 at 03:10 AM.

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Macro to sort column datasets based on totals

    This will suffice:

    Please Login or Register  to view this content.



  4. #4
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Macro to sort column datasets based on totals

    Hey Guys,

    Thanks for the macro but its not working quite how I want it for my large datasets... I don't want it moving the sorted data to a new sheet I want it overwriting itself... into the correct order.

    Thanks
    - Hyflex

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Macro to sort column datasets based on totals

    Are you kidding ?

    Delete the original sheet and rename the 'new' one to the original's one and you won't notice the difference. (unless you didn't tell us everything....)

  6. #6
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Macro to sort column datasets based on totals

    Quote Originally Posted by snb View Post
    Are you kidding ?

    Delete the original sheet and rename the 'new' one to the original's one and you won't notice the difference. (unless you didn't tell us everything....)
    It wont keep all of my other data in column A, B and other cells..?
    also your macro is coming up with a de-bug error :S i can't work out what is wrong
    Last edited by Hyflex; 10-06-2011 at 08:16 AM.

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Macro to sort column datasets based on totals

    Did you copy the macro ?
    Did you alter the macro ?
    In what line is the error ?

    To 'keep' your other data:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Macro to sort column datasets based on totals

    I've tried editing it for my main sheet and I can't get it to work, would anyone help with a few comments and its only working out half of my "datasets"
    Last edited by Hyflex; 10-06-2011 at 11:44 AM.

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Macro to sort column datasets based on totals

    Please do not quote (see the forum rules).

    You didn't answer any of my questions....

  10. #10
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Macro to sort column datasets based on totals

    Yes, I copied the macro & just renamed the sheet names.

    The error lies on:
    Please Login or Register  to view this content.
    However I am not sure if my other editing is correct because the columns, rows and size of sheet is much bigger and different

  11. #11
    Forum Contributor
    Join Date
    09-07-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: Macro to sort column datasets based on totals

    Quote Originally Posted by Hyflex View Post
    Yes, I copied the macro & just renamed the sheet names.

    The error lies on:
    Please Login or Register  to view this content.
    However I am not sure if my other editing is correct because the columns, rows and size of sheet is much bigger and different
    Bump, I still haven't found a solution for my ideals

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1