PDA

View Full Version : How to sort numbers and strings at once?



PandaKing66
07-19-2016, 08:23 AM
Hi,

I have a column of data that is pulled from a data server based on what the user inputs. This data needs to be sorted from largest to smallest (with largest at the top). My problem is sometimes the server returns "No Data" for some cells and it screws up the sorting. I have a descending sort in place which works perfectly on columns that do not include any strings. However, when a string is a part of the dataset, the string gets placed at the top and then the numbers are sorted. This is messing with graphs that are created because then there is a shift in the data.

My data looks like this:

16670



What I'm getting
What I want to get:


No Data
1


No Data
2


No Data
5


1
7


2
23


5
56


7
No Data


23
No Data


56
No Data

p45cal
07-19-2016, 09:15 AM
What's in the No Data cells? Pure text? Perhaps best to supply a workbook exhibiting this behaviour.

This data needs to be sorted from largest to smallest (with largest at the top)is at odds with "What I want to get:" showing the smallest at the top.

Kenneth Hobs
07-19-2016, 09:20 AM
If you are putting the data into Excel, then a sort using it would work. If you just want to work with arrays, a bubblesort would suffice. e.g.

Sub Main() Dim a() As Variant
a() = WorksheetFunction.Transpose(Range("A1:A9").Value)
MsgBox Join(a, vbLf)
BubbleSort a()
MsgBox Join(a, vbLf)
End Sub

Public Sub BubbleSort(ByRef InputArray, Optional SortColumn As Integer = 0, Optional Descending As Boolean = False)
' Sort a 1- or 2-Dimensional array.
Dim iFirstRow As Integer, iLastRow As Integer
Dim iFirstCol As Integer, iLastCol As Integer
Dim i As Integer, j As Integer, k As Integer
Dim varTemp As Variant, OutputArray As Variant
Dim iDimensions As Integer
iDimensions = ArrayDimensions(InputArray)
Select Case iDimensions
Case 1
iFirstRow = LBound(InputArray)
iLastRow = UBound(InputArray)
For i = iFirstRow To iLastRow - 1
For j = i + 1 To iLastRow
If InputArray(i) > InputArray(j) Then
varTemp = InputArray(j)
InputArray(j) = InputArray(i)
InputArray(i) = varTemp
End If
Next j
Next I
Case 2
iFirstRow = LBound(InputArray, 1)
iLastRow = UBound(InputArray, 1)
iFirstCol = LBound(InputArray, 2)
iLastCol = UBound(InputArray, 2)
For i = iFirstRow To iLastRow - 1
For j = i + 1 To iLastRow
If SortColumn = InputArray(j, SortColumn) Then
For k = iFirstCol To iLastCol
varTemp = InputArray(j, k)
InputArray(j, k) = InputArray(i, k)
InputArray(i, k) = varTemp
Next k
End If
Next j
Next i
End Select
If Descending Then
OutputArray = InputArray
For i = LBound(InputArray, 1) To UBound(InputArray, 1)
k = 1 + UBound(InputArray, 1) - i
For j = LBound(InputArray, 2) To UBound(InputArray, 2)
InputArray(i, j) = OutputArray(k, j)
Next j
Next i
Erase OutputArray
End If
End Sub


Function ArrayDimensions(InputArray As Variant)
'This function returns the number of dimension of the input array. It contains a loop that was _
'suggested in the .programming group by Dana DeLouis.
'Declare variables
Dim arr1, i As Integer, z As Long, msg As Variant
If Not TypeName(InputArray) Like "*()" Then
msg = "#ERROR! The function accepts only arrays."
If TypeOf Application.Caller Is Range Then
ArrayDimensions = msg
Else
MsgBox msg, 16
End If
Exit Function
End If
On Error Resume Next
'Loop until an error occurs
i = 1
Do
z = UBound(InputArray, i)
i = i + 1
Loop While Err = 0
'Reset the error value for use with other procedures
Err = 0
'Return the number of dimensions
ArrayDimensions = i - 2
End Function

PandaKing66
07-19-2016, 01:22 PM
huh... I can't believe I missed that. Yes, the "What I want to get" column should be ordered largest to smallest and then the strings.

Thanks

PandaKing66
07-19-2016, 01:29 PM
Kenneth, can you explain what you meant in the first part of your reply please? The bubblesort looks complicated and easy for me to mess up. If I can do it in excel then I'm going to go with that option.

Kenneth Hobs
07-19-2016, 02:24 PM
I see, you want a descending sort...

Some sort routines will fail when there are mixed cases like numeric and string and alphanumeric and numericalpha.

The Excel descending sort in that case would not be what you want nor would the descending option in bubblesort.

You want?



Have:
Want:


1No Data
5


2No Data
4


3No Data
3


4No Data
2


1
1


2
4No Data


3
3No Data


4
2No Data


5
1No Data




The want column was manually created.

The reason Excel does a descending sort with alphanumerics listed first as shown in the Immediate Window is:

?asc("0") 48
?asc("a")
97
?asc("A")
65
?asc("z")
122
?asc("Z")
90

Let me think on it. It would require a specific macro to do what you want. One might want alphanumerics listed first and some last and some might want to mix ascending and descending options for both numerics and alphanumerics.

FWIW: Don't be put off by code that is seemingly long and complicated. You can always poke them into a separate Module. Some long code is "long or complicated" because errors are coded so that it will skip or handle errors gracefully. e.g. The code for a built-in routine function like say =cell() might be much more involved than you might think. Howsoever, using =cell() works perfectly well and is simple. Part of that is because it is compiled code and part is that it was coded well as are most all of the built-in functions.

PM me in a few days if I have not responded and you have not received an answer. Sometimes I get an email notice from forum threads and sometimes not.

p45cal
07-19-2016, 03:06 PM
Again, what's in the No Data cells? Pure text? Perhaps best to supply a workbook exhibiting this behaviour.

snb
07-20-2016, 12:26 AM
Sub M_snb()
Columns(1).Replace "No Data", "", 1
Columns(1).Sort Cells(1), 1
End Sub

PandaKing66
07-21-2016, 08:49 AM
Again, what's in the No Data cells? Pure text? Perhaps best to supply a workbook exhibiting this behaviour.

Yes, the data returned is the string "No Data". snb, would you mind explaining your code?

p45cal
07-21-2016, 09:51 AM
Yes, the data returned is the string "No Data"
In that case snb's code is the way to go.
In the absence of a file to work on we can make snb's code more useable by making it work on the selected range (a single column). Select a range WITHOUT header first then run:
Sub M_snb()
Selection.Replace "No Data", "", 1
Selection.Sort Selection.Cells(1), 2
End Sub
If you still want to see No Data in cells and/or you have blank cells as well as No Data cells, and you want to keep them that way then a small tweak may be necessary.

PandaKing66
07-21-2016, 10:48 AM
In that case snb's code is the way to go.
In the absence of a file to work on we can make snb's code more useable by making it work on the selected range (a single column). Select a range WITHOUT header first then run:
Sub M_snb()
Selection.Replace "No Data", "", 1
Selection.Sort Selection.Cells(1), 2
End Sub

If you still want to see No Data in cells and/or you have blank cells as well as No Data cells, and you want to keep them that way then a small tweak may be necessary.


Wonderful, can one of you explain how this code works? I would rather understand how the code is working than just copy and paste it. I'm new to VBA and would really like to learn.

p45cal
07-21-2016, 11:40 AM
From the vb editor, press F1 and search for range.sort
likewise search for range.replace

PandaKing66
07-21-2016, 11:53 AM
From the vb editor, press F1 and search for range.sort
likewise search for range.replace

Ok, that helped but what are the "1" and "2" at the end of each line? I figured it was looking for "No Data" and replacing it with nothing but what does the "1" mean?

Kenneth Hobs
07-21-2016, 12:40 PM
The numbers are input parameter values. I recommend using the xl variable names for those constant values. The xl variable names are coded so that the name should say what it does.

If you record a macro and sort, you will see the syntax. A recording is the best way to learn. ? as first character in VBE's Immediate window and return will show what constant values are. e.g.

?xlAscending
1
?xlDescending
2
Think of ? as Debug.Print during runtime while ? in Immediate window is well, Immediate.

I did code a macro to sort either numbers or text and in same or separate order list either set first.

If you use the Replace() method, you might want to replace with a unique string so that you can Replace() that with No Data after the sort if that is needed. So, snb has shown you a simple method using Excel Sort method for a range. He is known for concise code.

PandaKing66
07-21-2016, 01:16 PM
Awesome thank you very much guys. I've come across another quick question pertaining to the .replace method. Some of the data I'm receiving is less than zero and I do not want to plot that. Can I use the replace method to replace anything that is less than zero with zero? Through my own, brief, research it looks like the replace method asks for a string as the "what to replace". I do not know if I can put some kind of expression there. You guys don't have to answer it if you don't want to, I am capable of looking into it myself, but I figured I'd ask some people who know the language really well.

p45cal
07-21-2016, 03:17 PM
Ok, that helped but what are the "1" and "2" at the end of each line? I figured it was looking for "No Data" and replacing it with nothing but what does the "1" mean?The 1 at the end of the replace method?
Well looking at Help:
range.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

Bold arguments are required, others are optional.
Selection.Replace "No Data", "", 1
The 1 is the 3rd argument. What's that? Oh, it's LookAt. What does that mean? Well the notes say:

LookAt, Optional, Variant, Can be one of the following XlLookAt constants: xlWhole or xlPart.

XlLookAt is a link, let's click it:

xlPart, 2, Match against any part of the search text.
xlWhole, 1, Match against the whole of the search text.

So it's telling the replace function to replace only No Data when that's all that is in the cell.

Teach a man to fish etc.

jolivanes
07-21-2016, 10:01 PM
Maybe another possibility? (see attached)

p45cal
07-22-2016, 03:09 AM
A variation of jolivanes solution using .SpecialCells:
Sub blah1()
With Range(Cells(1), Cells(Rows.Count, "A").End(xlUp))
.Sort Cells(1), 1, Header:=xlYes
.SpecialCells(xlCellTypeConstants, 1).Sort Cells(1), 2, Header:=xlNo
End With
End Sub

Answering the less than 0 values query, I think you're right that the replace method isn't going to cut it for that so this will first convert negative values to #N/A then do the sorting:
Sub blah2()
With Range(Cells(1), Cells(Rows.Count, "A").End(xlUp))
For Each cll In .Cells
If cll.Value < 0 Then cll.Value = [na()] 'instead of #N/A, anything else you want here.
Next cll
.Sort Cells(1), 1, Header:=xlYes
.SpecialCells(xlCellTypeConstants, 1).Sort Cells(1), 2, Header:=xlNo
End With
End Sub

and this converted to whatever you're selecting (still WITHOUT the header):
Sub blah3()
With Selection
For Each cll In .Cells
If cll.Value < 0 Then cll.Value = [na()] 'instead of #N/A, anything else you want here.
Next cll
.Sort .Cells(1), 1, Header:=xlNo
.SpecialCells(xlCellTypeConstants, 1).Sort .Cells(1), 2, Header:=xlNo
End With
End Sub

PandaKing66
07-22-2016, 01:07 PM
The 1 at the end of the replace method?
Well looking at Help:
range.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

Bold arguments are required, others are optional.
Selection.Replace "No Data", "", 1
The 1 is the 3rd argument. What's that? Oh, it's LookAt. What does that mean? Well the notes say:

LookAt, Optional, Variant, Can be one of the following XlLookAt constants: xlWhole or xlPart.

XlLookAt is a link, let's click it:

xlPart, 2, Match against any part of the search text.
xlWhole, 1, Match against the whole of the search text.

So it's telling the replace function to replace only No Data when that's all that is in the cell.

Teach a man to fish etc.
Wow, that was the most passive aggressive thing I could have possibly received from asking a simple question. I'm sorry that asking for a little help is too much trouble for you.

In every other "how to" article I have seen for VBA uses the Argument:=option1/option2/option3 format for optional arguments (LookAt:= xlWhole/xlPart, in this case). So I am sorry for not understanding a new format when I come across it.

Kenneth Hobs
07-22-2016, 01:58 PM
Actually, post 16 should have been extremely helpful to you and took extra effort. This is what help means. The next 2 posts solved your additional question. P45cal simply did a better job than I did explaining the possible input xl constants for Replace(). It is better to teach people concepts than just solving it. I do the same. When looking at command words you don't know, in the VBE, press F2 and browse or click in or near a word and press F1 to get specific help as post 16 showed.

Like the last posts, I too would iterate the cells to fix your negative value issue.

Since I am here, here is the routine that I made to sort text and numbers and order either set first.

Sub Test_advArrayListSort() Dim a(1 To 5) As Variant, b As Variant
a(1) = 55
a(2) = 1
a(3) = 1
a(4) = "a"
a(5) = "z"

b = a()
'b = UniqueArrayByDict(a(), tfStripBlanks:=True)

'b = advArrayListSort(a(), tfNumbersFirst:=False)
'b = advArrayListSort(b)
'b = advArrayListSort(b, False, False)
'b = advArrayListSort(b, True, False)
b = advArrayListSort(b, True, False, False)

MsgBox Join(b, vbLf)
End Sub


'https://msdn.microsoft.com/en-us/library/system.collections.arraylist(v=vs.110).aspx
Function advArrayListSort(sn As Variant, Optional tfAscending1 As Boolean = True, _
Optional tfAscending2 As Boolean = True, _
Optional tfNumbersFirst As Boolean = True) As Variant

Dim i As Long, c1 As Object, c2 As Object
Dim a1() As Variant, a2() As Variant, a() As Variant

Set c1 = CreateObject("System.Collections.ArrayList")
Set c2 = CreateObject("System.Collections.ArrayList")

For i = LBound(sn) To UBound(sn)
If IsNumeric(sn(i)) = True Then
c1.Add sn(i)
Else
c2.Add sn(i)
End If
Next i

c1.Sort 'Sort ascendending
c2.Sort 'Sort ascending

If tfAscending1 = False Then c1.Reverse 'Sort and then Reverse to sort descending
If tfAscending2 = False Then c2.Reverse 'Sort and then Reverse to sort descending

a1() = c1.Toarray()
a2() = c2.Toarray()

If tfNumbersFirst = True Then
a() = a1()
For i = 1 To c2.Count
ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = a2(i - 1)
Next i
Else
a() = a2()
For i = 1 To c1.Count
ReDim Preserve a(UBound(a) + 1)
a(UBound(a)) = a1(i - 1)
Next i
End If

advArrayListSort = a()
End Function

p45cal
07-22-2016, 02:00 PM
I'm sorry that asking for a little help is too much trouble for you.You got it didn't you? And more to boot. Such grace.

snb
07-23-2016, 03:11 AM
If you are too lazy to use all options the VBEditor, a VBA handbook and/or a course VBA basics provide, your are the only one who is to blame.
Laziness is the principal enemy of knowledge.

Aussiebear
07-23-2016, 06:43 PM
If you are too lazy to use all options the VBEditor, a VBA handbook and/or a course VBA basics provide, your are the only one who is to blame.
Laziness is the principal enemy of knowledge.

Was your post necessary snb? There's a strong possibility here that the OP has not experienced all of the options contained within the VBEditor, for reasons other than your perceived "laziness". I believe that you need to withdraw your remark.