Sorting alphanumerical column in numeric order

It's me again - sorry to trouble you but I can't solve this.

Column H has a text only (address) - Primary sort column
Column G has text and numbers, sometimes only numbers - secondary sort column
Column B has text only

I need a macro to sort the 3 columns with column G in numerical order.

Example:
Col B Col G Col H
Jones house 12 in Main Street, Town
Smith 24 Main Street, Town
Mills 63a Ring Road, Town

Obviously, Col H has to be the primary sort column so that all the same street names are kept together. This is OK on the normal sort option. However, col G is required in numerical order so that the result will be 1 to (say) 88 Main Street, Town.

I am so befuddled with this that I can't even pretend to have some sort of code.

Thanks for any help.

I too am confused, as I didn't see you had posted before. Sorry.

Now - if you could attach a sample workbook I could look at your problem and most likely give you a good answer.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by Raphaelp

Why don't you add a column and put the house number in that column? That would make the job much easier.

This would probably be my approach, as well

Originally Posted by MarvinP

I too am confused, as I didn't see you had posted before. Sorry.

Now - if you could attach a sample workbook I could look at your problem and most likely give you a good answer.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Marvin, thanks for joining this post. Attached is a sample of what I have in my workbook (sheet1). In the sample sheet 1 is similar to what I have and sheet 2 is what I need out of the code/ A few more pieces of info on the sheets.

You will find some further info on the worksheets - Sheet 1 is what may be found in my data while sheet 2 is what I hope to have when sorted - each number in numerical order i.e. 1 before 10, and and 2 before 20, etc.

I don't see the attachment, so I'll have another go at that.

Hi teelnich. Many thanks for all the work you must have done i producing that code. Most of it I don't understand as there are a lot of functions (?) used which I have never seen before.

Your first version worked asfar as producing the extra column I. However when I tried to use the sort code I got an error 438 (I think) - "object doesn't support this property or method". This occurred on the first line of the sort code

Formula:

Please Login or Register  to view this content.

This is the line that gets highlighted when I try to debug.

I have tried, for many hours and searching on the internet to try and resolve the problem but to no avail.

Perhaps the uploaded file will help.

Again, many thanks for all the work you hav eput into this for me. I tried your second offering and that certainly, it looks much neater. Using the excel Sort doesn't give what I need.

My advice to you is as follows:

Put this in column I:

Please Login or Register  to view this content.

and then sort first by column H, then by column I, (then by column B, if you so desire) and click on the popup "Sort anything that looks like a number as a number."

This should do what you are trying to accomplish.

Put this in a Standard module and run Sub tst.

Please Login or Register  to view this content.

Hi Thadacto- Try this:

Please Login or Register  to view this content.

Make sure you change the Workbook and Worksheet references. Good luck!

You're welcome and thanks for rep+.