Alphanumeric Sorting for multiple columns

mydaymysay · Jun 5th 2013

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

Display More

mydaymysay · Jun 6th 2013

Re: Alphanumeric Sorting for multiple columns

Can anyone help me with this?

mydaymysay · Jun 6th 2013

Re: Alphanumeric Sorting for multiple columns

I look forward to receiving some help on this.

**cytop** · Jun 7th 2013

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.

mydaymysay · Jun 11th 2013

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.

**cytop** · Jun 11th 2013

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...

ashu1990 · Jun 11th 2013

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

**cytop** · Jun 11th 2013

Re: Alphanumeric Sorting for multiple columns

Also: http://stackoverflow.com/quest…ting-for-multiple-columns

Alphanumeric Sorting for multiple columns

Participate now!

Share