Sort data 1,2,3,4 1,2,3,4 NOT 1,1,2,2,3,3,4,4

Hello Folks,

I am trying to create a VBA macro to sort data in a spreadsheet in the following order:
1,2,3,4
1,2,3,4
1,2,3,4
5,6

Rather than the traditional 111,222,333,444,5,6 that you get through the normal Sort options.

Thank you in advance,

John

Can you upload a workbook with an example of how the data is laid out and hwo you want the results to be arranged. Click go advanced and then manage attachments to upload a workbook.

Also how would 11122233344455666777888899 be sorted?

I presume your data is in one column, but it is confusing how you have laid it out in your description.

I would suggest having a helper column with a formula like:

=COUNTIF(A$1:A1,A1)

so when you copy this down then an "occurrence" number is generated. Then you want to sort using this as the key field and the other numbers as the secondary field.

Hope this helps.

Pete

@ Pete I originally thought along those lines but think that would give 12345612341234 not 12341234123456... Maybe sort by the helper column descending and then by the number column ascending might work? I'm not sure what OP wants...

Hi Guys,

Thank you so much for your input so far. Please see a workbook showing how the data currently is, and how i want it to be.

John
example.xlsx

Hi John,
maybe so

Please Login or Register  to view this content.

Put this formula in C2:

=IF(LEFT(A2)*1>4,1000+ROWS($1:1),COUNTIF(A$2:A2,A2))

then copy down to the bottom of your data. Then sort using column C as the first sort field and column A as the second sort field. Then you can delete column C.

Hope this helps.

Pete