Function Not Working - Sort & Remove Duplicates by Creating New Array


New Member
Mar 18, 2021
Office Version
  1. 365
  1. Windows
I am not well versed in VBA and have no idea where to start finding the issue here.

I need to write a function that will take a 1 dimensional array as an input and spit out a new array with all the values sorted alphabetically and duplicates removed. This input "SourceList" column has text fields and combination of numbers/text (mostly text) and it has a lot of duplicates. I do not want to alter the original list and I need to keep it dynamic so if new data is added the resulting array will update automatically. Please help!!!

FYI: I tried writing a test at the end so I could attempt debugging but it fails with a "Run-time error '1004': Application-defined or object-defined error" at the "testcells = Worksheets..." line. Also not sure if this is related but when I hit either F5 or the Run button it pulls up the "Macros" dialog box instead, and the only macro on the list is the "test" procedure.

FYI#2: I wrote something that does the trick without VBA, but it calculates way too slow to be a realistic solution. In case it's helpful that array formula is:
Excel Formula:
The input list is in Worksheet "NamedSheetExample" H2:1999 (I changed the formula reference to 100000 so it catches new data being added, but that's not my preferred method if it can be hard-coded to always find the end of the list). I wrote/entered the formula starting in cell AJ4, so the AJ$3:AJ3 is to check the cell entries previous to the current one in order to make sure everything is sorted properly.

BIG THANK YOU in advance to whomever can help me with this.

VBA Code:
Function ListUniqueSorted(SourceList() As Variant) As Variant
'Outputs a sorted array of only the unique items from a list
'SourceList can be any length or order and have duplicates

Dim coll As New Collection, item As Variant
Dim tempArray() As Variant
'Defines collection and array to temporarily store items from SourceList

Dim i As Long, first As Long, last As Long
first = LBound(SourceList)
last = UBound(SourceList)
'Get first and last array positions

On Error Resume Next
For Each item In SourceList
    coll.Add item, item
'Adds SourceList items to collection using item as both the value and key
'Keys must be unique so duplicates will not get added to collection

last = coll.Count + first - 1
ReDim tempArray(first To last)
'Define/Resize the array to match number of unique items in collection

For i = first To last
    tempArray(i) = coll(i - first + 1)
Next i
'Populates temporary array using collection with duplicates removed

Dim j As Long, k As Long, sortArray As Variant
    For j = LBound(tempArray) To UBound(tempArray) - 1
        For k = j + 1 To UBound(tempArray)
            If UCase(tempArray(j)) < UCase(tempArray(k)) Then
            sortArray = tempArray(k)
            tempArray(k) = tempArray(j)
            temArray(j) = sortArray
            End If
        Next k
    Next j
'Sorts array alphabetically

ListUniqueSorted = tempArray
'Result is list of unique items sorted alphabetically

End Function

'test for debugging
Sub test()
    Dim testcells() As Variant
    testcells = Worksheets("NamedSheetExample").Range("H2:100000")
    MsgBox ListUniqueSorted(testcells)
End Sub
'test for debugging

...It actually would be ideal if I could modify this to work with a multi-dimensional array to depend on other columns, since this list is further broken up into categories. The above (if it worked) would give me a master list of everything, but below is my actual end goal - with the master list in column AJ. In the full data set (not sorted, with lots of duplicates) on sheet NamedSheetExample there are two fields that further break down each entry into associated categories (eg. LOCATION1, LOCATION 2,... and subcategories A, B,C,...). I compare each category's list side-by-side with the master by leaving blanks in the corresponding rows and filling the rest (the checkmarks and x's are meant as a summary, I actually have other tabs A/B/C... to hold those subcategory lists separately, both to do further analysis and to avoid repeating words). Just taking it one step at a time. Appreciate any help. Thank you!!!

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I don't have the capability to add XL2BB but here's an example of what the source data would look like.

rubber bandLOCATION3B
sun glassesLOCATION1D
lamp shadeLOCATION2A
Upvote 0
New Excel Formulas saved the day! Fast performance on the calculations and no need to use VBA.
Excel Formula:

I only added the IF statement because there was an unexplained 0 showing up at the end of the master list. I did not have this issue any other time using the formula throughout my spreadsheet,
- However, in these instances I was also applying a filter. For example:

Excel Formula:

I was able to use named ranges in the first case but the formula would not work (gave #VALUE error) when I tried using named ranges with the filters. No issues with named ranges anywhere else. Also, I love that these new array formulas do not require ctrl+shift+enter and automatically update according to the size of the array! I first tried entering the formula while intentionally oversizing the destination array so that any new data added would not get cut off, but that gave (#N/A) once it reached the end of the unique list. I COULD NOT get rid of it with "IFNA" or "IFERROR", not sure why. In the end there was no need to increase the array size in anticipation of new data being added; I got my desired result just by simply hitting enter and letting Excel size the array.

FYI: I was able to fill in the rest of the table summarizing the categories/subcategories by using variations of INDEX and MATCH, with the lookup value for MATCH=1 because of criteria being applied to the lookup array. Example:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back