+ Reply to Thread
Results 1 to 7 of 7

Manual sort macro

  1. #1
    Forum Contributor
    Join Date
    02-26-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    173

    Manual sort macro

    Thank you for your attention to my thread.

    I have spreadsheet that I have to sort the rows in. I have already recorded an autosort to put the spreadsheet into alphabetical format but I need a slight modification of the sort. The spreadsheet is not very long so it shouldn't be much issue to sort it twice unless you know of a better way.

    Here is a sample that shows what a typical spreadsheet might look like.
    spreadsheet.png

    What I need is a way to move the MN and URS rows to the top of the list. In this case, it would end up in an order similar to this:

    MN 12345
    MN 123456
    URS 23456
    URS 234567
    ARB 123A
    WRD 1234

    It does not matter which of MN or URS is at the top, but MN needs to stay grouped together as does URS. I already have the following code cycling through the rows.
    Please Login or Register  to view this content.
    Thank you so much!

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Manual sort macro

    smpita,

    You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,979

    Re: Manual sort macro

    Please Login or Register  to view this content.
    Ben Van Johnson

  4. #4
    Forum Contributor
    Join Date
    02-26-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    173

    Re: Manual sort macro

    Attached (rawsortexample.csv) is a spreadsheet that needs to be custom sorted by my VBA macro. Post macro, it should look like sortexampledone.csv

    In column B, you see several different values in the format "Community" (URS, MN, ARB, etc) then an "Account number" (1234a, etc) then a date. I need to sort it so that the URS and MN communities are on top and everything else is on the bottom. My example shows ARB, CM and WRD communities but we are often adding new communities so I can't use the CustomOrder: method suggested by protonLeah above as we would need to be constantly updating the macro to include new communities. I was thinking something to the effect of pseudocode:

    Please Login or Register  to view this content.
    The communities will already be sorted by the following code and be in alphabetical order. I just need to move the URS and MN accounts to the top of the pile somehow.
    Please Login or Register  to view this content.
    Here is the part of my macro that deals with this report in full.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by smpita; 03-04-2014 at 07:10 PM.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Manual sort macro

    You could use a two-column lookup table with communities on the left column and sort order on the right, and then use a lookup formula in a convenient column. Sort on that.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Contributor
    Join Date
    02-26-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    173

    Re: Manual sort macro

    shg - I'm not sure I know what you meant but this is what I came up with from your suggestion. It seems to work, but I'm not sure if it's the most efficient code. Thank you!

    Please Login or Register  to view this content.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Manual sort macro

    Using code for this lacks flexibility and is overkill. I had in mind just a table and a formula:


    A
    B
    C
    D
    E
    F
    G
    H
    1
    Comm
    Acct
    Date
    Sort
    Comm
    Order
    2
    MN
    2
    D2: =IFERROR(VLOOKUP(A2, $G$2:$H$4, 2, 0), 99) URS
    1
    3
    URS
    1
    MN
    2
    4
    XXX
    99
    ARB
    3
    5
    MN
    2
    6
    XXX
    99
    7
    XXX
    99
    8
    URS
    1
    9
    XXX
    99
    10
    MN
    2
    11
    ARB
    3
    12
    MN
    2
    Last edited by shg; 03-05-2014 at 08:07 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to sort wthout doing the final manual manipulations?
    By plumberef in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-14-2013, 07:43 AM
  2. Macro for automating the manual stuff
    By shruthidwh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-04-2011, 11:16 AM
  3. Reorder Rows (Manual Sort), Copy, Insert, Delete Row
    By Orada in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-17-2011, 03:32 PM
  4. Running Macro Overrides Manual Commands
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-30-2010, 04:25 PM
  5. Macro with manual file selection?
    By Pmedina in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-23-2005, 05:50 PM

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