PDA

View Full Version : Sorting by number in non-numeric field



kronik
02-23-2006, 10:12 AM
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
02-23-2006, 10:57 AM
Any chance you can relabel them as:

Building#-PC### ?

Patrick

kronik
02-23-2006, 10:59 AM
You mean PC001 PC002

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

matthewspatrick
02-23-2006, 11:01 AM
Yes, that was exactly what I meant. Oh well, time for the complicated solution :motz2:

matthewspatrick
02-23-2006, 11:06 AM
OK, add these UDFs:



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/Applications/MS_Office/Excel/Q_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







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
02-23-2006, 11:09 AM
Can you use a helper column?

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

=MID(A1,9,1)+0

matthewspatrick
02-23-2006, 11:10 AM
Norie,

Just shoot me now :banghead:

Very nicely done,

Patrick

matthewspatrick
02-23-2006, 11:14 AM
Sorry, Norie, that doesn't work.

Norie
02-23-2006, 11:14 AM
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
02-23-2006, 11:18 AM
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
02-23-2006, 11:18 AM
Sorry, Norie, that doesn't work.

Works for me.:think:

matthewspatrick
02-23-2006, 11:24 AM
Norie,

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

Should be PC2...

kronik
02-23-2006, 11:27 AM
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
02-23-2006, 11:33 AM
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
02-23-2006, 11:39 AM
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
02-23-2006, 11:40 AM
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
02-23-2006, 11:42 AM
Patrick

I did get PC2 first.

I must have been doing something wrong, then :whistle:

Patrick

kronik
02-23-2006, 12:06 PM
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
02-23-2006, 03:58 PM
="RS"&TEXT(StripOutCharType(RetrieveSplitItem(A1,"H",1,FALSE),FALSE),"00000000")&"H-"&StripOutCharType(RetrieveSplitItem(A1,"-",2))&TEXT(StripOutCharType(RetrieveSplitItem(A1,"-",2),FALSE),"00000000")

kronik
03-03-2006, 12:32 PM
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? ;)

matthewspatrick
03-03-2006, 12:38 PM
kronik,

We're all very busy volunteers here, so how about you take a shot at implementing this into a macro, come back with what you've got, and we'll help you clean it up?

XLGibbs
03-03-2006, 05:24 PM
What you describe can be recorded. Like Patrick said, we are all fairly busy but can likely help you do some housekeeping or troubleshooting with a recorded version of what you describe. Adding a button is simple, just drag one from the control toolbox or forms toolbar and draw it onto the sheet, with a control, you can put the code in the button_Click event. With a forms toolbar, you can just assign the code by referring to the macro in the Assign Macro dialog when right clcking the button.

kronik
03-03-2006, 07:06 PM
Not a problem at all, actually..

I'm just having trouble figuring out how to accomodate for a dynamic range. Like, if I make a dynamic range, how do I get the sum total of rows in the range of say the A column?

From a logic standpoint, I think I have it..

So, have a dynamic named range for the first column as well as the sort column. Paste the formula across the sort column for as many entries as there are in the A range. Sort by the column.. then, delete the entries for all cells up to the number of rows.

Norie
03-04-2006, 08:57 AM
No need for a dynamic named range, though that might be useful for other things.

The following should put a simple formula in each row in column B that has a value in column A.

LastRow = Range("A"&Rows.Count).End(xlUp).Row
Range("B1:B"& LastRow).Formula ="=A1"