+ Reply to Thread
Results 1 to 4 of 4

Sorting mixed content.

Hybrid View

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2003
    Posts
    48

    Sorting mixed content.

    I have a worksheet with a row of mixed cells near the top.
    By "mixed", I mean some cells have only numeric data (numeric-cells) and some have only words (word-cells).

    GOAL: I want to sort the columns to the following order:
    First all the columns with only numeric-cells in the mixed row, with those columns sorted in DESCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order.

    So far all I have been able to accomplish is:

    First all the columns with only numeric-cells in the mixed row, with those columns sorted in ASCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order. This was accomplished by a simple sort using ASCENDING order on the mixed row.

    OR

    First all the columns with only word-cells in the mixed row, with those columns sorted in DESCENDING alphabetic order of the word-cells in the mixed row; followed by the columns with only numeric-cells in the mixed row sorted by DESCENDING numeric order. This was accomplished by a simple sort using DESCENDING order on the mixed row.

    This may sound as confusing to you as it does to me, so I have attached an example file showing hypothetical Input Data and the Desired Result.

    I did the example file in multiple steps, but that's too painful in the general case where calculations determine what is in which cell in the mixed row. All data are calculated, even the word-cell data AND so dependent on other data, and the columns in the mixed row that are word-cells and those that are numeric cells don't remain the same.

    Eventually I'd like to do this with a macro, so if VBA has some power to do this that EXCEL commands don't, then that's fine.

    I could probably live with the final result as numeric-cell columns sorted in DESCENDING order followed by word-cell columns sorted in DESCENDING order, but I haven't even been able to accomplish that AND it's not quite the goal.

    Thanks in advance for any help you can give,
    metsci
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: Sorting mixed content.

    My "formula proposition" - of course, you can register macro and then fine-tune it.

    in C3 formula:
    =IF(ISNUMBER(C4),-C4,C4)
    and copy right
    now select whole table with extra row and sort ascending order left-to-right with row 3 as key.
    Delete all formulas in row 3

    Enjoy!
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: Sorting mixed content.

    Well, I did registered a macro and tuned it a bit. So select all your table (without headers) and run:

    Sub Test()
    Dim ul As Range, lr As Range
    Application.ScreenUpdating = False
    With Selection
      Set ul = .Cells(1)
      Set lr = .Cells(.Cells.Count)
    End With
    ul.EntireRow.Insert Shift:=xlDown
    Set ul = ul.Offset(-1, 0)
    ul.FormulaR1C1 = "=IF(ISNUMBER(R[1]C),-R[1]C,R[1]C)"
    Range(ul, Cells(ul.Row, lr.Column)).FillRight
    Range(ul, lr).Sort Key1:=ul, Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
    ul.EntireRow.Delete
    End Sub
    of course instead of selecting before running macro you can use other methods to select upperlefy (ul) and lowerright (lr) corners of your data table (As I said - just data, not headers).

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Sorting mixed content.

    Hi metsci,

    1. To solve your problem manually:
    a. Sort the entire range by row 4 ASCENDING. This will put the text in order in the proper columns.
    b. Then sort 'C4:H7' by row 4 DESCENDING. This will put the numbers in the proper columns.

    2. The following Macro will do the same thing as the manual sort:
    Sub SortDataByRowsUsingTheSameRangeAllTheTime()
      'This sorts the range using row 4 as a key:
      'a. Numbers is descending order
      'b. Followed by text in ascending order
      '
      'as generated by the Macro Recorder and modified for 'Pretty Print'
    
      Range("C4:M7").Select
      Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
    
      Range("C4:H7").Select
      Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
    
    End Sub
    3. See the attached file for a Macro that is probably a good starting point for your final needs.
    The macro uses variables to define the 'range' of the sort area and the 'sort key'.

    Please feel free to ask if you need help with any of the code or you have any more questions.

    Lewis
    Attached Files Attached Files

+ 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. Sorting mixed data cells
    By JorisDDS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-19-2014, 05:44 PM
  2. Replies: 11
    Last Post: 02-11-2013, 08:10 AM
  3. Replies: 4
    Last Post: 08-25-2012, 07:49 AM
  4. Sorting mixed numeric values
    By cellophane in forum Excel General
    Replies: 6
    Last Post: 05-31-2012, 07:35 AM
  5. ZeroPadding Mixed Number, Text, Number Content in Cell
    By Sbubendorf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2008, 05:52 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