Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Alphanumeric Sorting for multiple columns

1. I agreed to these rules
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. I agreed to these rules
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. I agreed to these rules
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. ## 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. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

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

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Alphanumeric Sorting for multiple columns

try this
VB:
```Sub test()
range("A1").select
ActiveCell.Offset(0, 1).Select
Loop
End Sub

```
hope this helps

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Alphanumeric Sorting for multiple columns

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

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

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