Link to home
Start Free TrialLog in
Avatar of Cartillo
CartilloFlag for Malaysia

asked on

Consolidate Data

Hi Experts,

I would like to request Experts help create a code to consolidate data at "Date Query" sheet based on data set at "Order" sheet. The search date queries are entered at cell C3 and C5 (Date Query) sheet. All titles which were booked under the selected date (Start/End Date) from Order sheet need to be displayed.

I have manually created a sample data at "Date Query" sheet for Experts to get a better view. Hope Experts will help me create this feature.

OrderList-V2.xlsm
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cartillo

ASKER

Hi dlmille,

Thanks a lot of detailing each process, it’s very clear for me to understand how the whole script works to generate each line.
After testing few data I noticed the query not return if the query is crossed from current year to next year. E.g.

Start Date: 21-Nov
End Date: 31-Jan (2012)

Is that any possibility to make this query feasible.
Avatar of Kevin Cross
dlmille,

Could it be the date format? I will leave you to help Cartillo, but was just passing by and saw the issue. I would try it with date format: YYYY-MM-DD or 2011-11-21. That way the start and end date comparisons behave more appropriately respect to chronology versus potentially doing an alphanumeric sort.

Kevin
Sure - it all has to do with the formatting of the dates.  I had to add YY to the date designation in the sheet for the dropdown and in the code to discriminate between years and it now works with multiple years.

Be sure to credit my original post as well, so matthewspatrick's article http:/A_3802.html gets credit.

See attached,

Cheers,

Dave
OrderList-V2-r5.xlsm
Hi Dave,

Thanks for the revised code. When I search data for 1-Aug 2011 to 31-Aug 2011, data for 1-Jan 2011 to 20-Feb 2011 also appeared. How to make only query result being displayed.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Dave,

Thanks a  lot for helping me create this booking tools.

Hi mwvisa1,

Thanks for the comment.
Cartillo - any need to sort the output - e.g., by Activity?  Let me know, as I could write a quick sort function in the class (hadn't done that as yet, so was wondering if this was the opportunity).

Dave
Hi Dave,

First of all, thanks for your willingness. Actually, I have another issue with this workbook, which is deleting unwanted data based on Date/Time. The macro able to delete a specific data based on the Date filter but not with a specific time and frequency. Hope you could help me to enhance this feature. I'll ask a new question for this. Please consider.
Hi Dave,

Hope you will consider this Q:(pertaining with the same workbook)

https://www.experts-exchange.com/questions/27246880/Delete-Data-at-specific-time.html

For the record, I created a Sort function to sort the Activities class collection.  

You can do:

Activities.Sort to sort ascending, Activities.Sort(False) to sort descending.

This was my first attempt at doing some of this, but looks like good results.  Happy to have any E-E experts comment.

The code in question, under the clasee clsActivities is as follows (just for expert comment), in particular the functions: ItemSorted, Exists, Key, and Sort.

Exists - to mimic the Dictionary.Exists function
Key - to mimic the Dictionary.Key function
Sort - to sort the Activities Class collection (it doesn't change the collection, but updates the Act_Rank property so that one can loop through in Ranked fashion)
ItemSorted - to return the Activity based on an Index, looking at the Act_Rank property

' dmbw, CVX April, 2011
' Created 2011-08-08

' Parent collection class of clsActivity

'Option Explicit
Option Compare Text

' Container for all clsActivity objects in the parent collection class
Private coll As Collection

Private Sub Class_Initialize()
    
    Set coll = New Collection
    
End Sub

Private Sub Class_Terminate()
    
    Set coll = Nothing
    
End Sub

Public Function Add(Act_Name As String) As clsActivity
    
    ' Adds a new item to the collection.  Causes an error if an item with the same key already exists
    ' or if you pass a zero length string for the Act_Name argument
    
    If Act_Name = "" Then
        Err.Raise vbObjectError + 1002, , "Act_Name property of clsActivity object cannot be zero length string"
    End If
    
    Set Add = New clsActivity
    Add.Act_Name = Act_Name
    coll.Add Add, Act_Name
    
End Function

Public Sub Clear()
    
    ' Recreates (and thus clears) collection
    
    Set coll = New Collection
    
End Sub

Property Get Count() As Long
    
    ' Returns number of items in the collection
    
    ' Read-only
    
    Count = coll.Count
    
End Property

Property Get Item(Index As Variant) As clsActivity
    
    ' Default property.  Returns an item from the collection.  Index may be either ordinal position (Long) or Act_Name (String)
    
    ' Read-only
    
    On Error Resume Next
    Set Item = coll(Index)
    On Error GoTo 0
    
End Property

Property Get ItemSorted(Index As Variant) As clsActivity
Dim myItem As Object

    If Not coll Is Nothing Then
        
        On Error Resume Next
        For Each myItem In coll
            If myItem.Act_Rank = Index Then
                Set ItemSorted = myItem
                Exit Function
            End If
        Next myItem
        On Error GoTo 0
    End If
    
End Property
Public Sub Remove(Index As Variant)
    
    ' Removes an item from the collection.  Index may be either ordinal position (Long) or Act_Name (String)
    
    coll.Remove Index
    
End Sub

Function NewEnum() As IUnknown

    ' Enables enumeration of the clsActivities parent collection, i.e.:
    '
    ' For Each Child In Parent...Next
    
    Set NewEnum = coll.[_NewEnum]
End Function

Function Exists(Act_Name As String) As Boolean
Dim myItem As Object

    If Not coll Is Nothing Then
        For Each myItem In coll
            If myItem.Act_Name = Act_Name Then
                Exists = True
                Exit Function
            End If
        Next myItem
    End If
    
    Exists = False
End Function

Function Key(Act_Name As String) As clsActivity
Dim myItem As Object

    If Not coll Is Nothing Then
        For Each myItem In coll
            If myItem.Act_Name = Act_Name Then
                Set Key = myItem
                Exit Function
            End If
        Next myItem
    End If
    
    Key = Null
End Function

Function Sort(Optional sortAscending As Boolean = True)
Dim tmp As Variant, myAct As Object
Dim i As Long
Dim changed As Boolean
Dim tmpColl As New Collection

'Simple bubble sort approach...

'create a temporary collection for sorting
    For Each myAct In coll
        tmpColl.Add myAct.Act_Name
    Next myAct
    
'now sort that collection
    Do
        changed = False
        For i = 1 To tmpColl.Count - 1
            If sortAscending Then
                If tmpColl(i) > tmpColl(i + 1) Then
                    tmp = tmpColl(i + 1)
                    tmpColl.Remove i + 1
                    tmpColl.Add tmp, tmp, i
                    changed = True
                End If
            Else
                If tmpColl(i) < tmpColl(i + 1) Then
                    tmp = tmpColl(i + 1)
                    tmpColl.Remove i + 1
                    tmpColl.Add tmp, tmp, i
                    changed = True
                End If
            End If
        Next i
    Loop Until Not changed

'now update the Act_Rank property of the Activities class collection

    For i = 1 To tmpColl.Count
        For Each myAct In coll
            If myAct.Act_Name = tmpColl(i) Then
                myAct.Act_Rank = i
                Exit For
            End If
        Next myAct
    Next i
    
    Set tmpColl = Nothing
End Function

Open in new window


See attached "final" submital for the knowledgebase.

I'll take a look at your outstanding question, to see if I can help.

See attached.

Cheers,

Dave
OrderList-V2-r7.xlsm
Very nicely done, Dave!