Sorting by number in non-numeric field

[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-PC100
Building2-PC1
Building2-PC2
Building2-PC100
Building3-PC1
Building3-PC2
Building3-PC100

Now how do I get there? :P

[matthewspatrick]

Any chance you can relabel them as:

Building#-PC### ?

Patrick

[kronik]

You mean PC001 PC002

Nope, can't change the data... got like 5000 entries.

[matthewspatrick]

Yes, that was exactly what I meant. Oh well, time for the complicated solution

[matthewspatrick]

OK, add these UDFs:



[VBA] Option Explicit

Public Function RetrieveSplitItem(Text As String, Separator As String, Item As Variant, _
Optional CaseSen As Boolean = False)

' Function based on post by Brad Yundt
' http://www.experts-exchange.com/Appl..._21221177.html

' Returns a specified substring from a larger string (Text) separated by a specified
' character sequence (Separator)
Dim X As Variant
If CaseSen Then
X = Split(Text, Separator, -1, vbBinaryCompare)
Else
X = Split(Text, Separator, -1, vbTextCompare)
End If

If IsNumeric(Item) And (Item < 1 Or Item > (UBound(X) + 1)) Then
RetrieveSplitItem = CVErr(xlErrNA)
ElseIf Not IsNumeric(Item) And Item <> "L" And Item <> "l" Then
RetrieveSplitItem = CVErr(xlErrNA)
Else
If Item = "L" Or Item = "l" Then Item = UBound(X) + 1
RetrieveSplitItem = X(Item - 1)
End If

End Function

Public Function StripOutCharType(CheckStr As String, Optional KillNumbers As Boolean = True, _
Optional AllowedChar As String, Optional NeverAllow As String) As String

' Function by Patrick Matthews
' For the given string, the function removes all numeric characters (KillNumbers=True) or
' all non-numeric characters (KillNumbers=False). Use AllowedChar to build a string of override
' characters that are always allowed. For example, "$,." would indicate that the dollar sign,
' comma, and period should all be allowed, even if KillNumbers=False; likewise, "9" would indicate
' that nines should be kept even if KillNumbers=True. NeverAllow is a string of override
' characters that are never allowed. The "never allowed" characters are processed before the
' "always allowed" characters, and so if any characters are in both strings Never allow takes
' precedence

Dim Counter As Long
Dim TestChar As String
Dim TestAsc As Long

' Loop through characters
For Counter = 1 To Len(CheckStr)

' Get current character and its ANSI number
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)

' Test first to see if current character is never allowed
If InStr(1, NeverAllow, TestChar, vbTextCompare) > 0 Then
' do nothing

' If current character is in AllowedChar, keep it
ElseIf InStr(1, AllowedChar, TestChar, vbTextCompare) > 0 Then
StripOutCharType = StripOutCharType & TestChar

' If KillNumbers=True, test for not being in numeric range for ANSI
ElseIf KillNumbers Then 'only allow non-numbers
If TestAsc < 48 Or TestAsc > 57 Then
StripOutCharType = StripOutCharType & TestChar
End If

' If KillNumbers=False, test for being in numeric ANSI range
Else 'only allow numbers
If TestAsc >= 48 And TestAsc <= 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
End If
Next

End Function

[/VBA]





Now, in another column, enter this formula:

=RetrieveSplitItem(A1,"-",1)&"-"&StripOutCharType(RetrieveSplitItem(A1,"-",2))&TEXT(StripOutCharType(RetrieveSplitItem(A1,"-",2),FALSE),"000")

(Assumes your data starts in A1)

Now, sort on that new column.

Patrick

[Norie]

Can you use a helper column?

If so put this formula in, copy down and sort by it.

=MID(A1,9,1)+0

[matthewspatrick]

Norie,

Just shoot me now

Very nicely done,

Patrick

[matthewspatrick]

Sorry, Norie, that doesn't work.

[Norie]

Patrick

That solution relies on there only being single digit for the building.

We'll need to see if that's the case when the OP posts back.

Knowing my luck it won't be especially considering there are 5000 records.

[matthewspatrick]

If the Building# is always 1 digit, and if PC# is 1-100 digits, then use two helper columns:

=MID(A1,9,1)+0 This sorts building
=MID(A1,13,100)+0 This sorts PC

If the Buidling# can vary in digits, I'd need to adapt my original formula...

[Norie]

Sorry, Norie, that doesn't work.

Works for me.

[matthewspatrick]

Norie,

Which comes first for you: Building1-PC2 or Building1-PC100?

Should be PC2...

[kronik]

Neither solution works..

Let me give you some sample data.

See attached.

1st row is sample data, 2nd is how it sorts via Excel, 3rd row is desired sort order.

I can use a helper column if necessary.. preferably hidden but not mandatory.

The problem with Norie's solution is that it basically simplifies the data to a point where many are equal.. so Building2-PC1 = Building3-PC1.. (arbitrary numbers).. meaning both = 1 in the helper column.

Patrick's solution still does not order them properly, at least in my implementation.

[Norie]

kronik

I used this in column B

=MID(A1,3,1)+0

and this in column C

=MID(A1,9,255)+0

I then sorted on column B and column C and it appears the results are correct.

Patrick

I did get PC2 first.

[kronik]

Ahh I see what yours does now.. I didn't have the second column that actually figures out what number the PC is.. just the one that figures out what building it is.

I think that worked. Thanks.

[matthewspatrick]

kronik,

The examples from your question do not match up with the stuff in the sample workbook.

Change my formula to:

=RetrieveSplitItem(A1,"-",1)&"-"&StripOutCharType(RetrieveSplitItem(A1,"-",2))&TEXT(StripOutCharType(RetrieveSplitItem(A1,"-",2),FALSE),"00000")

I just tested it, and it worked.

Patrick

[matthewspatrick]

Patrick

I did get PC2 first.

I must have been doing something wrong, then

Patrick

[kronik]

Ah ok, retried yours matthew and I worked.. sorry, I should have been more specific with my example.

Question though.. say I have RS1H, RS11H, RS12H, and RS2H.. and I want 1-2-11-12.. can that be done with this sort method?

Also,

any way to make this column dynamic to 1st column so when the first column changes, so does this one..? I would assume dynamic named ranges may aid me in that but I'm not sure how I would manipulate them.

[matthewspatrick]

="RS"&TEXT(StripOutCharType(RetrieveSplitItem(A1,"H",1,FALSE),FALSE),"00000000")&"H-"&StripOutCharType(RetrieveSplitItem(A1,"-",2))&TEXT(StripOutCharType(RetrieveSplitItem(A1,"-",2),FALSE),"00000000")

[kronik]

Alright..

So, how about a button that - counts the number of rows in column A, sets up the sort column with the appropriate number of rows, sorts by the column, then deletes the column?