Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Alphanumeric Sorting for multiple columns

  1. #1
    Join Date
    6th June 2013
    Posts
    4

    Alphanumeric Sorting for multiple columns

    Can anyone help me with an alphanumeric sorting code for multiple columns sorting (column by column sorting) advance.

    Also let me know If the following code can be used for multiple columns:

    VB:
    Sub sortAlphaNumericMulti() 
         
        Dim i As Long 'Loop counter
        Dim lrow As Long 'Last row of data
        Dim nrow As Long 'New lastrow after copy
        Dim lcol As Long 'Last col of data, this row
         
         'Presumes data in Col A and Row 1 has maximum extents
         
         'Get last row of data Col A (+1 for first blank row)
        lrow = Range("A65536").End(xlUp).Row + 1 
         
         'Variable for compare
        nrow = lrow 
         
         'Get last column of data (row 1)
        lcol = Range("IV1").End(xlToLeft).Column 
         
         'Use empty space as storage, splitting cell values
         
         'Loop on all rows
        For i = 1 To lrow - 1 
             'Get alpha
            Cells(nrow, 1) = Left(Cells(i, 1), 3) 
             'Get numeric
            Cells(nrow, 2) = Left(Cells(i, 1), Len(Cells(i, 1)) - 3) 
             'Copy rest of data
            Range(Cells(i, 2), Cells(i, lcol)).Copy Cells(nrow, 3) 
             'Increment row
            nrow = nrow + 1 
        Next i 
         
         'Sort by numeric then alpha
        Range(Cells(lrow, 1), Cells(nrow - 1, lcol + 1)).Select 
        Selection.Sort _ 
        Key1:=Range("B" & lrow), Order1:=xlAscending, _ 
        Key2:=Range("A" & lrow), Order2:=xlAscending, _ 
        Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
        False, Orientation:=xlTopToBottom 
         
         'Copy/Paste back
         'where to work
        nrow = lrow 
         'Loop on all cells
        For i = 1 To lrow - 1 
             'Rebuild original values
            Cells(i, 1) = Cells(nrow, 1) & Cells(nrow, 2) 
             
             'Copy sorted
            Range(Cells(nrow, 3), Cells(nrow, lcol + 1)).Copy Cells(i, 2) 
             
             'Increment row
            nrow = nrow + 1 
        Next i 
         
         'Delete temp(working)range
        Range(Cells(lrow, 1), Cells(nrow - 1, lcol + 1)).ClearContents 
         
        Application.ScreenUpdating = True 
         
    End Sub 
    
    
    Last edited by Smallman; June 6th, 2013 at 15:21. Reason: Add code Tags to post

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th June 2013
    Posts
    4

    Re: Alphanumeric Sorting for multiple columns

    Can anyone help me with this?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th June 2013
    Posts
    4

    Re: Alphanumeric Sorting for multiple columns

    I look forward to receiving some help on this.
    Last edited by cytop; June 6th, 2013 at 21:04. Reason: Removed quoted post.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st September 2010
    Posts
    7,916

    Re: Alphanumeric Sorting for multiple columns

    Same rules here as on Excelforum - Please wait a sensible period before bumping a thread.

    But a rule more rigorously enforced is:

    Rule #4 - Do not cross-post without supplying a link to the duplicate question on the other forum.

    You also posted this topic on ExcelForum. While you are free to post where ever, every board like this has a similar rule - you must include links to all you posts on other boards.

    Have a read of this to understand the reasons for this rule.

    You should update this thread with the URL of any other threads dealing with this particular issue, and ensure future posts by you comply fully with the rules.
    Last edited by cytop; June 7th, 2013 at 23:48.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    6th June 2013
    Posts
    4

    Re: Alphanumeric Sorting for multiple columns

    Dear All,

    I have attached a sample of requirements.

    My data is in the format "# = XXXX" where # can be any number between 1 to 500 and XXXX can any number alphabets. I have to sort the data in descending order based on the digits. I am not concerned about the order of the alphabets. Also, the number of columns in the worksheet is very large (more than 1000). So I cant not work on individual columns. I think only a macro can help me here. I hardly have any experience in coding. The code I shared in my earlier email extract the numbers from a cell and sorts the first column based on the numbers. But it works for a single column only. I am not sure how to modify the code for it to work for 1000 of columns at a go. Thanks in advance.




    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    1st September 2010
    Posts
    7,916

    Re: Alphanumeric Sorting for multiple columns

    Please acknowledge my earlier message and update your thread with the URLs of any other post regarding this issue...

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th June 2013
    Location
    India
    Posts
    148

    Re: Alphanumeric Sorting for multiple columns

    try this
    VB:
    Sub test() 
        range("A1").select 
        Do While ActiveCell.Address <> Range("IV1").End(xlToLeft).Offset(0, 1).Address 
             ''''your code here
            ActiveCell.Offset(0, 1).Select 
        Loop 
    End Sub 
    
    
    hope this helps

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    1st September 2010
    Posts
    7,916

    Re: Alphanumeric Sorting for multiple columns

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 15
    Last Post: February 12th, 2013, 00:44
  2. Replies: 7
    Last Post: July 21st, 2012, 00:11
  3. Counting unique values, 2 columns, alphanumeric
    By cdhowells in forum EXCEL HELP
    Replies: 5
    Last Post: January 16th, 2007, 23:16
  4. Sorting Alphanumeric Text
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: December 11th, 2006, 12:55
  5. Sorting multiple columns
    By mzp in forum EXCEL HELP
    Replies: 3
    Last Post: July 22nd, 2004, 01:38

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
  •  
porno