Sorting in Excel

[VQCHEESE]

I have a column that i need to sort by. its a number column

right now it looks like this. Is there a way i can get it like this:
1300 1300
1301 1301
1500 13002
1804 1500
6004 15000
8002 1804
13002 18045
15000 6004
18045 8002
80001 80001
80004 80004

is there any way to sort like that?

 

[Excelenator]

If your data is in Column A and you insert a column before it so that it is now in column B you can insert this formula into Column A and copy it down the length of your data. Then sort on column A and delete column A for your final result.

=B1/((10)^LEN(B1)-1)

 

[giacomo]

can you explain the logic of your sort? It's neither numeric or alpha sorted...

 

[VQCHEESE]

the reason behind my sort is because at our company we have producer numbers, but if they have more then one Bulktank that is the last number.
so if Producer X's number is 8000 but he has 4 bulk tanks then he has 4 differnt lab numbers ex...8000,80001,80002,80003, and we want them all to stay together when we sort

does that clarify?

 

[giacomo]

yes, are the producer #'s always 4 digits?

 

[VQCHEESE]

Not all of them, but quite afew of them are

 

[VQCHEESE]

oh wait never mind i looked at the wrong comlumn yes ALWAYS 4 DIGITS

 

[giacomo]

why does 1301 come before 13002 in your example shouldn't it be 1300, 13002, 1301?

 

[VQCHEESE]

Yeah thats my mistake yes it should.
are you geting anywhere with this one?

 

[giacomo]

if that's the case Excelenator's formula should have worked for you, I only continued on because I thought it was producing the wrong result based on your bad example.

I would use the following personally, only because I'm too dim to get my head around what Excelenator is doing...

=IF(LEN(b1)>4,b1/10,b1)

hth,
Giacomo