Announcement

Collapse
No announcement yet.

Alphanumeric Sorting for multiple columns

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Code:
    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, 15:21. Reason: Add code Tags to post

  • #2
    Re: Alphanumeric Sorting for multiple columns

    Can anyone help me with this?

    Comment


    • #3
      Re: Alphanumeric Sorting for multiple columns

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

      Comment


      • #4
        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, 23:48.

        Comment


        • #5
          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

          Comment


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

            Comment


            • #7
              Re: Alphanumeric Sorting for multiple columns

              try this
              Code:
              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

              Comment


              • #8
                Re: Alphanumeric Sorting for multiple columns

                Also: http://stackoverflow.com/questions/1...ltiple-columns

                Comment

                Working...
                X