Sort Column based on certain numbers

[rendon115]

I figured Id ask here before the VBA forums.

The quick version is Im trying to move rows in a very specific manner. The first column contains numbers that are 4 digits long. If they are preceded with the number 1, I have to see if the other three digits match any of the rest of the rows. If it does, I need to drop the 4 digit number with 1 under the matching one. i.e.

Starting sheet:
0001 |...|...
0002 |...|...
0003 |...|...
0004 |...|...
1001 |...|...
1002 |...|...

Desired look:
0001 |...|...
1001 |...|...
0002 |...|...
1002 |...|...
0003 |...|...
0004 |...|...

Each entry of 0001 - 0999 are unique values.

The idea would be to check to the right of 1 for values. For example, 1999 would drop the one and then check 999 and compare it for an exact match using the remaining three characters.

This is also in a table.

The first XXXX only happens when theres two of the same part(number), we denote that as the opposite here.

Thank you,

[ThxAlot]

Hi, there

You can use VALUE(MID(A1,2,3)) to extract numbers, then sort the numbers in an ascending order.

pls refer to the formulae in the attached file.

[rendon115]

This would work in theory, but the numbers in the rows are not 0001, or 0002. Theyre formatted as 4 numbers in excel, so the actual values are just 1 and 2, respectively. So in theory,

Please Login or Register  to view this content.

would be the idea. I dont know how to move rows around, based on a matching value in excel using formulas though.