So, yea, I'm new. And, I did some searching, but couldn't find anything like what I'm looking for. I want to take a list of strings (names usually) in one column that will change based on other items in the spreadsheet, and have them sorted automatically in another column to be used as a drop down list elsewhere.
I'd been doing this with a macro, where after the list changes, you run the macro and the available names are resorted. But, I want to get rid of that, and have it done automatically, without having to either sort them using excel, or running a macro.
I was looking around, and if they were numbers, I could do that easily with the LARGE or SMALL functions, so I was thinking I needed something like that, but for text. My search-fu failed me, and I couldn't find anything for it. So, I made my own? Hopefully, this turns out to be useful once I start using it at work.
I browsed through some other UDF's and borrowed some techniques for converting ranges into array's of values within a function (thanks al_b_cnu) and then wrote what I think is a basic bubble sort, since I couldn't find anything on a built in function to sort the array for me.
There's a few things I want to fix with it, but it's working for now. These will help to speed it up, I think.
a) I want to make the array sizes match what's coming into the function. When I try and define an array without a value in the () I just get an error back when using the function. But, I can't seem to figure out how to set the size of the array using a variable. I have to choose a number to start with, and stick with it. This works at 1000 where I have it right now, but it's too large for most of the time, and if I try and do something with a larger dataset, it won't be adequate. Plus, I don't know how this is going to work if I just feed it an entire Column of data.
b) I would like to make, what i'm calling the nested secondary sort, adjust to the size of the data. Like, right now, to help speed it up, I have it first check 5 records back to see if it needs to move a chunk of data backwards instead of forwards. That's set at 5 specifically, but for bigger sections of data, a larger number would be more appropriate.
c) Even if the array size is matched to the size of the incoming data, if there are any blank cells, they float (bubble sort, lol) to the top of the list. So, I have to redo the final result before outputting it. Can anyone see a better way to do that then what I have at the end of the function there already? Maybe a way to chop off all of the entries at the first of the array? Such as, find that the first non-blank entry is #369, then chop off entries 0-368? I haven't had any success with that so far.
Any help or advice is appreciated, thanks...
-- jason
I'd been doing this with a macro, where after the list changes, you run the macro and the available names are resorted. But, I want to get rid of that, and have it done automatically, without having to either sort them using excel, or running a macro.
I was looking around, and if they were numbers, I could do that easily with the LARGE or SMALL functions, so I was thinking I needed something like that, but for text. My search-fu failed me, and I couldn't find anything for it. So, I made my own? Hopefully, this turns out to be useful once I start using it at work.
I browsed through some other UDF's and borrowed some techniques for converting ranges into array's of values within a function (thanks al_b_cnu) and then wrote what I think is a basic bubble sort, since I couldn't find anything on a built in function to sort the array for me.
There's a few things I want to fix with it, but it's working for now. These will help to speed it up, I think.
a) I want to make the array sizes match what's coming into the function. When I try and define an array without a value in the () I just get an error back when using the function. But, I can't seem to figure out how to set the size of the array using a variable. I have to choose a number to start with, and stick with it. This works at 1000 where I have it right now, but it's too large for most of the time, and if I try and do something with a larger dataset, it won't be adequate. Plus, I don't know how this is going to work if I just feed it an entire Column of data.
b) I would like to make, what i'm calling the nested secondary sort, adjust to the size of the data. Like, right now, to help speed it up, I have it first check 5 records back to see if it needs to move a chunk of data backwards instead of forwards. That's set at 5 specifically, but for bigger sections of data, a larger number would be more appropriate.
c) Even if the array size is matched to the size of the incoming data, if there are any blank cells, they float (bubble sort, lol) to the top of the list. So, I have to redo the final result before outputting it. Can anyone see a better way to do that then what I have at the end of the function there already? Maybe a way to chop off all of the entries at the first of the array? Such as, find that the first non-blank entry is #369, then chop off entries 0-368? I haven't had any success with that so far.
Code:
Function NthItemInSortedList(ByVal IncomingData As Range, _
ByVal IndexNum As Integer) As Variant
'---------------
' ** Written by Je1330, 03/25/2010
'---------------
Dim IndividualString As Range
Dim i As Integer
Dim i2 As Integer
Dim EndRow As Long
Dim ComparisonA As String
Dim ComparisonB As String
Dim ComparisonC As String
Dim Complete As Boolean
Dim NextNonBlank As Integer
'---------------
' I set the size of the two array's at 1000, but would like to
' find a way to have this variable to the size of the incoming
' dataset, so as to keep memory usage down and speed up the
' function should it be used for REALLY large lists...
'---------------
Dim ArrayOfStrings(1000) As String
Dim SortedResult(1000) As String
'---------------
' First I use this loop to populate ArrayOfStrings with the
' data from the range defined by IncomingData.
' Not sure about how these properties work, but I borrowed
' the code from several other UDF's I've been experimenting with.
'---------------
EndRow = IncomingData.Rows.Count
i = 0
For Each IndividualString In Range(IncomingData.Cells(1, 1), IncomingData.Cells(EndRow, 1))
ArrayOfStrings(i) = IndividualString
i = i + 1
Next IndividualString
'---------------
' This section actually performs the bubble sort.
' In order to speed it up some, I included a secondary
' comparison that looks at the item 5 entries before the current
' line first, skipping ahead if needed.
' This MUST improve it a small portion, but unless we're
' looking at really large data sets, it might not ever be noticed.
' Even better would be a way to adjust the 'backlash' from that
' secondary sort to better match the size of the array, such as
' 1/10th of the size maybe, instead of set right at 5 all the time.
'---------------
Complete = False
Do While Complete = False
' Mark Complete, but one swap will set to False
Complete = True
i2 = 0
Do While i2 < 1000
ComparisonA = ArrayOfStrings(i2)
ComparisonB = ArrayOfStrings(i2 + 1)
If i2 > 5 Then
ComparisonC = ArrayOfStrings(i2 - 5)
If ComparisonA < ComparisonC Then
ArrayOfStrings(i2 - 5) = ComparisonA
ArrayOfStrings(i2) = ComparisonC
Complete = False
i2 = i2 - 6
Else
If ComparisonA > ComparisonB Then
ArrayOfStrings(i2 + 1) = ComparisonA
ArrayOfStrings(i2) = ComparisonB
Complete = False
i2 = i2 + 1
End If
End If
Else
If ComparisonA > ComparisonB Then
ArrayOfStrings(i2 + 1) = ComparisonA
ArrayOfStrings(i2) = ComparisonB
Complete = False
i2 = i2 + 1
End If
End If
i2 = i2 + 1
Loop
Loop
'---------------
' Find first NONBLANK entry in array, because blanks are floated to
' the beginning during the sort. We'll then match the SortedResult
' to the actual sorted data at the end of our ArrayOfStrings.
'---------------
NextNonBlank = -1
i2 = 0
Do While Len(ArrayOfStrings(i2)) < 1
i2 = i2 + 1
Loop
NextNonBlank = i2
i2 = 0
Do While NextNonBlank <= 1000
SortedResult(i2) = ArrayOfStrings(NextNonBlank)
NextNonBlank = NextNonBlank + 1
i2 = i2 + 1
Loop
'---------------
' We have the array, return the requested result number.
' Note that for ease of use, IndexNum is decremented by 1 to match the array.
'---------------
NthItemInSortedList = SortedResult(IndexNum - 1)
End Function
Any help or advice is appreciated, thanks...
-- jason