Cartillo
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Dave,
Thanks a lot for helping me create this booking tools.
Hi mwvisa1,
Thanks for the comment.
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
Dave
ASKER
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.
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.
ASKER
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
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
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
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
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!
ASKER
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.