Alphanummerical aligning of numbers (addition to previous tread)

Hi guys,
This tread is in addition to a previously which you guys solved for me (http://www.excelforum.com/excel-gene...nsolidate.html)
To summerize the previous tread, i'm dealing with a large amount of data in excel, which every month has to be aligned with the (numerical) account numbers from our ERP-system.

So my new headache is that we want to expand the Datawarehouse so it is able to contain alphanumerical account numbers (numbers and letters), which complicates the macro. Previously the macro perfectly aligned the account numbers in nummerical order. But when we add letters to this, it puts the alphanummerical numbers at the end of the macro. I want to numbers to primary determine the order, and letting the letters be the secoundary determinant.

Current situation (with alphanummerical numbers):
100 100 597,5359479
200 200 184,4143015
1000
2100 2100 690,3195229
4210
100-BB
100-CC 600,6907594
100-DD 100-DD 25,86463684
100-AA 100-AA 959,2499921
200-AA 404,4266519
200-CC
200-AA

Where row 1 and 2 is being aligned and row 3 is random numbers following row 2.
Seems like this letter formating is random (since BB is above AA)

Correct formating:
100 100 597,5359479
100-AA 100-AA 959,2499921
100-BB
100-CC 600,6907594
100-DD 100-DD 25,86463684
200 200 184,4143015
200-AA 200-AA 404,4266519
200-CC
1000
2100 2100 690,3195229
4210

Please have a look at the attached excel file (the above is a bit blurry)

Below you see the macro made by stanleydgromjr (which works perfect without alphanummerical numbers)

Please Login or Register  to view this content.

I really hope you are able to help me with this, Thanks in advance!

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I'll change it for you on this occasion but please note for the future.

Perhaps a simpler non macro way, (although you could of course encode the following standard Excel formula in a macro and automate it) is a helper column.

In B1 and copied down.

Formula:

Please Login or Register  to view this content.

Now sort columns A & B with B as the primary key and A as the secondary, chosing the 'Sort anything that looks like a number as a number' option.

Note that in your original the two rows that contained 1000 & 4210 were numbers rather than text when I copied and pasted them. For consistency you may want to ensure that in your production system all the rows are text.

padrald0w,

Thanks for the Private Message.

I have trie1d to work with your present data. But, column A's and C's data is a combination of text and numbers.

I have used Richard Buttrey's formula in extra columns to the right to re-sort the data and then put it back without any blank rows.

But, when I adjust the prior macro, the code does not work correctly because how do you compare if text is <> a number, and vice versa.

I do have some other code that I will look at later today.

padrald0w,

Ok, here you go.

I am using columns K, L, and M, as a work area (which will be cleared after the macro).

With your latest workbook, worksheet Sheet1's formatting:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the AlignAccountNbrV2 macro.

padrald0w,

I have helped you before (see your attached link).

jindon's code is very fast. And, another for my archives to study and learn something new.

One slight change to jindon's code (see BOLDED code lines) to put the output in the right location.

Please Login or Register  to view this content.