Sorting by number in a non-numeric field

  • Thread starter Thread starter kronik
  • Start date Start date
K

kronik

Hi guys.. so question..

Say I have a row with example:

Building1-PC1
Building2-PC1
Building3-PC1
Building1-PC2
Building2-PC2
Building3-PC3
Building1-PC100
Building2-PC100
Building3-PC100..

Desired result from a sort..

Building1-PC1
Building1-PC2
Building1-PC3
Building2-PC1
Building2-PC2
Building2-PC3
Building3-PC1
Building3-PC2
Building3PC-3

Now how do I get there? :P
 
kronik,

There is no way to do that by sorting.
Sorting cannot change text.

Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



in message
Hi guys.. so question..
Say I have a row with example:

Building1-PC1
Building2-PC1
Building3-PC1
Building1-PC2
Building2-PC2
Building3-PC3
Building1-PC100
Building2-PC100
Building3-PC100..

Desired result from a sort..

Building1-PC1
Building1-PC2
Building1-PC3
Building2-PC1
Building2-PC2
Building2-PC3
Building3-PC1
Building3-PC2
Building3PC-3

Now how do I get there? :P
 
Hi guys.. so question..

Say I have a row with example:

Building1-PC1
Building2-PC1
Building3-PC1
Building1-PC2
Building2-PC2
Building3-PC3
Building1-PC100
Building2-PC100
Building3-PC100..

Desired result from a sort..

Building1-PC1
Building1-PC2
Building1-PC3
Building2-PC1
Building2-PC2
Building2-PC3
Building3-PC1
Building3-PC2
Building3PC-3

Now how do I get there? :P
Click to expand...

Your example is incomplete. You show results in the sorted section that don't
exist in your original data!

But you can use two helper columns to do what I think you might want.

To sort numerically by building number, and then by PC number, with your data
in A1:An,

B1:
=--LEFT(SUBSTITUTE(A1,"Building","",1),FIND("-",SUBSTITUTE(A1,"Building","",1))-1)

C1:
=--MID(A1,FIND("-PC",A1)+3,5)

Select B1:C1 and copy/drag down to Bn:Cn

Then select A1:Cn
Data/Sort
Column B -- Ascending.
Column C -- Ascending

See if that gives you what you want.

After sorting, you can hide or delete the two helper columns.


--ron
 
Hey there..

Yea, you caught it before I edited it at 12:38. ;)

I got that solution to work.. is there any way to do it dynamically?

So that Bn/Cn are determined by what An is.. without having to go to
B/C and copy/paste.
 
Hey there..

Yea, you caught it before I edited it at 12:38. ;)

I got that solution to work.. is there any way to do it dynamically?

So that Bn/Cn are determined by what An is.. without having to go to
B/C and copy/paste.
Click to expand...

You could just leave the formulas in B1:Cn, and copy the new data into column
A.

If you don't want to see the #VALUE! error on the blank cells, you could either
use conditional formatting to "white it out", or change the formula to return a
null string instead of an error:

B1:
=IF(ISERR(FIND("Building",A1)),"",--LEFT(SUBSTITUTE(A1,
"Building","",1),FIND("-",SUBSTITUTE(A1,"Building","",1))-1))

C1:
=IF(ISERR(FIND("Building",A1)),"",--MID(A1,FIND("-PC",A1)+3,5))

Is that what you want?

If you want to have dynamically sorted data in B1:Bn without going through the
data sort routine, that's a bit more complicated but certainly doable with a
freely available add-in.
--ron
 
Back
Top