Excel VBA ranking and copy and paste order

[dluhut]

Hello excel vba experts,

I have a file that I download through company's intranet, however, data was shows as individual "tabular" form. My manager doesn't like the use of formula and have asked me to create a macro that when he download the file, with the macro that I (well you guys actually) created, it will automatically sort the data and still keeping the "look" and "format" of the data.

In my attached sample on 'Original', that's how the data looks like when it was downloaded. In the current sample, I only have 2 QUOTES

Please Login or Register  to view this content.

Notice that most string are the same, except the last '4' digits (5212 and 5219). Each of these digits will have an alphabet character (a, b, c & d) to make it "unique". Since when the report downloaded was not in order (I have create column 'G' for you to understand the ranking), is there a way for a macro to "sort" this and still keep the "look" and "format" of the data - in other words, I would like the macro to have the final result as 'DESIRED RESULT'

Criteria to rank is

1. Ascending order on the last '4'digit'
2. Follow by 'a', 'b', 'c' & 'd' for

e.g1. 5212 is rank '1st' compared to '5219'.
e.g2. 5212a is rank '1st', 5212b is rank '2nd', 5212c is rank '3rd' and 5212d is rank '4th'