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:
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.
...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!!!
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:
{=IFERROR(INDEX($H$2:$H$100000,MATCH(0,COUNTIF($H$2:$H$100000,"<"&$H$2:$H$100000)-SUM(COUNTIF($H$2:$H$100000,AJ$3:AJ3)),0)),"")}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
Next
'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!!!
