0

I have two columns of dates up to 30k lines. I want to filter for one date in the second column and return all the unique dates from the first column.

I could build this in Access but I don't use Access for anything else so it seems overkill to use that. And partly because I am curious if I can do it in Excel. I'd prefer not to use a loop because it will be expensive in terms of time to run and I am just learning about arrays and class modules so this is a great example.

There is some background on this already on SOF but it's not detailed enough for me - I'm unfamiliar with Class Modules Filtering 2D Arrays in Excel VBA

Any help or pointers would be greatly appreciated.

Sub CreateUniqueTradeDatesForAsOfDate_test()
Dim InternalArray As Variant
Dim Rg_Internal As Range
Dim arr As New Collection
Dim myRow As Long
Dim myCol As Long

Set d = CreateObject("Scripting.Dictionary")
'set the range
Set Rg_Internal = Worksheets("Bloomberg").Range("G:H")

'Set the array to the range
InternalArray = Rg_Internal.Value
'Transpose the array
InternalArray = Application.Transpose(InternalArray)

   'Create the unique
    With CreateObject("scripting.dictionary")
    For Each it In InternalArray
        d = .Item(it)
    Next
    d = .Keys ' the array .keys contains all unique keys
End With

'print to the immediate window but all unique values of the array 
' not just the unique values from the first column based on 
'the criteria from the second column
     For Each i In d 'To UBound(10, 1)
       Debug.Print i; RowNos
    Next i

 End Sub
Community
  • 1
  • 1
BMoore
  • 49
  • 4
  • If you loop arrays then its extremely fast and the "loop an array approach" is also offered in the linked post you gave us. If you are looking for further optimization then you're probably on the wrong site and should post your working / existing code on [Code Review](http://codereview.stackexchange.com/) instead. They will do their best to optimize your code in terms of speed, security, sustainability, and longevity including best-practices. – Ralph Mar 16 '17 at 15:55
  • Try Chip Pearsons [Introduction to Classes](http://www.cpearson.com/Excel/Classes.aspx) for a starter. – Ron Rosenfeld Mar 16 '17 at 15:57
  • I just edited the post with the code I have so far. I'm not looking for further optimization, I'm just unfamiliar with how to incorporate that approach into a working filter. Thanks for the Chip Pearson post. I'd seen that already but I was hoping for an example I can follow more closely. – BMoore Mar 16 '17 at 18:37

0 Answers0