Find Nth item in list of strings, after sorting them

[je1330]

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.

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

 

[Norie]

Jason

It might help if you broke this down a bit.

You've posted a lot of text and a lot of text.

Also you seem to want to use a UDF to change what's on a worksheet - you can't really do that.

The purpose of a UDF in Excel is to return a value, not change what's on a worksheet.

 

[je1330]

Mmmm, maybe I do need to explain it a bit more, thanks.

In this example screenshot above, Column D will change to a Yes or No value based on a selection on another tab. When each value changes, the list in Column C will change based on if the name is available for selection or not. Because of the way the data is pulled from another report, it's difficult to change the order of the list, but displaying a name, or not displaying a name, is pretty simple. (actually done off of the person's supervisor)

Now, in Column A, the function isn't going to change the data in any other sections, only list the currently available choices from Column C in alphabetical order.

To do that, I call the function like this in cell A2...

=nthiteminsortedlist($C$2:$C$10, ROW()-1)

The range is given first, C2:C10, with $'s so that it won't change, and then I use "ROW()-1" to say that I want the 'first' item, then the 'second' item and so on as it's copied down the column.

When the available names in Column C change, the ones listed in Column A update automatically as the function is recalculated.

I hope that helps, am I still over doing it, could I make the data in Column A update easier, but still without using a macro?

EDIT: Sorry, and the reason for this is that Column A is then used as validation for a drop down list. When a different supervisor is chosen, the list will update to include only that supe's team members, with them listed in alphabetical order.

-- Jason