I have 4 Collection objects of the following kind:
dates1=(12/01/02, 15/01/03, 17/01/03, 25/04/04...) dates2=(15/02/02, 17/06/07, 10/05/10, ...) numbers1 = (3,5,2,7,...) numbers2 = (1,-3,-1,...)
The rules of the game are:
dates1will always have the same length of
dates2will always have the same length of
dates1is not necessarily as long as
The dates into the two
datescollections are always growing within the same collection, but not necessarily the first date of
dates1will be before the first date of
Problem in words
I would like to create a "merged" collection of these two. In particular,
datesTotcollection will have to contain only one date for each record. So, for example, if both "dates1" and "dates2" contain the 14/01/13, then this date will appear in the collection only once.
numbersTotcollection will have to contain the sum of the two "numbers" collection in case the date is a duplicate, otherwise only the single value.
numbers1 = (1,3,4) numbers2 = (4,7,12) dates1 = (13/01/14, 14/01/15, 16/01/17) dates2 = (12/01/14, 14/01/15, 18/01/17)
will result in the following:
datesTot = (12/01/14, 13/01/14, 14/01/15, 16/01/17, 18/01/17) numbersTot = (4, 1, 10, 4, 12) '<-- where 10 is the sum of 7+3, because they both are on 14/01/15
What I have tried so far
I have tried to stretch the human logic described above in a code:
Determining with which date the joint collection should start:
Do While (dates1Index <= dates1Index.Count) And (dates2Index <= dates2Index.Count) If dates1(dates1Index) < dates2(dates2Index) Then '<-- to put in a while loop with some conditions check datesTot.Add dates1(dates1Index) numbersTot.Add numbers1(dates1Index) dates1Index = dates1Index + 1 ElseIf dates1(dates1Index) > dates2(dates2Index) Then datesTot.Add dates2(dates2Index) numbersTot.Add numbers2(dates2Index) dates2Index = dates2Index+1 Else datesTot.Add dates1(dates1Index) numbersTot.Add numbers1(dates1Index) + numbers2(dates2Index) dates1Index = dates1Index + 1 dates2Index = dates2Index + 1 End If Loop
The idea, hence, is to keep on looping through the elements like this with the separate indexes, by adding some controls (for the collections lengths) and once reached the shortest collection's length, keep on adding all the others of the longest collection.
The more I look at this method, the less I'm convinced. It's heavy, dirty and (in my opinion) not the best algorithm ever. Is there any built in for collections in VBA to join the elements without duplicates and sort them (I've not found anything in the docs) hence? Or in general, how should I design my code to accomplish this purpose in a flexible way?