CODEWITHSUNDEEP
vbadateexcelunique
sar
sar

Reputation: 37

find unique dates in excel vba array

I've got a data set that contains multiple measurements for each day. I've already put the data in a VBA array. Now, I would like to create an array with the unique days. 

_Here is my dataset:_  
12/07/2010     3.5

12/07/2010     2.1

12/07/2010     2.2

12/08/2010     5.2

12/08/2010     3.2

12/09/2010     4.7

Here is what I would like to return:

12/07/2010

12/08/2010

12/09/2010

I've looked at a few user defined functions for unique values, but they operate on numerical values, not date types.

Any help is greatly appreciated. Thanks!

Upvotes: 1

Views: 5297

Answers (3)

Tiago Cardoso
Tiago Cardoso

Reputation: 2097

For filtering purposes, I'd use a Collection. I created a mock vArray which would represent your own array.

Collections don't accept the same key twice, therefore would be filtering out dup values. Due to the 'On Error resume next' I strongly recommend you to have this piece of code separately in one specific function.

Sub test()

    Dim vItem As Variant
    Dim vArray(5) As Variant

    vArray(0) = CDate("12/07/2010")
    vArray(1) = CDate("12/07/2010")
    vArray(2) = CDate("12/07/2010")
    vArray(3) = CDate("12/08/2010")
    vArray(4) = CDate("12/08/2010")
    vArray(5) = CDate("12/09/2010")

    Dim colDates As Collection

    Set colDates = New Collection

    On Error Resume Next

    For Each vItem In vArray
        colDates.Add vItem, CStr(vItem)
    Next vItem

End Sub

Upvotes: 2

Charles Williams
Charles Williams

Reputation: 23530

The Date type is a number...

An easy way is to use a collection:

Sub Uniques()
    Dim oColl As New Collection

    Dim vArr As Variant
    Dim vItem As Variant
    Dim j As Long
    vArr = Range("A1:B6")
    On Error Resume Next
    For j = LBound(vArr) To UBound(vArr)
        oColl.Add vArr(j, 1), CStr(vArr(j, 1))
    Next j
    On Error GoTo 0

    For Each vItem In oColl
        Debug.Print vItem
    Next vItem
End Sub

Upvotes: 2

Patrick
Patrick

Reputation: 7712

There is no simple way to do this functin.

The easiest I can think of is using the Filter function

Filter returns an array containing the values from the array that match the filter.

so Logically:

arTargetArray as Array

For each Item in your_array
  value = Filter(arTargetArray, Item)
  if value null
    add item to  arTargetArray.
  end if
next each

hopefuly this helps a little bit.

i know this syntax has nothing to do with vba, its just the logical approach I would personally take.

Upvotes: 1

Related Questions