adding selected rows into a sheet with a specific selection

[albe.btz]

Hi forum,

i've another exciting question for you (and my desperation to heal).

So, in the benchmark model attached (part of it due to size limits)

benchmark UK variations fix.xlsm

the file has been completely ligthened of data an features to fit the forum's limit but you should be able to get all the information.

a bit of background: whilst Expanded periods, and GANNT are sheets regenerated everytime i run a macro "Variations" works as a database to record all the fare variations.
it has to take some lines from Expanded periods and store them at the bottom of the sheet.

what I am unable to do is to implement a module that makes it happen: this module has to find and select the specific row for different seasonality (LOW,SHO,BUS) for each CXR (QF,EY,SQ,MH,CX,BA,QR) for each destination (SYD, MEL, AKL).

Then it has to copy and paste it a the bottom of VARIATIONS inserting the date when the module has ran (this to build the history of variations).


HELP MEE PLEASSEEEEEEE

[D_Marcel]

I did not think in any VBA solution due to my lack of time in the present moment, but have you thought about create a key field by concatenate these three columns, and then eliminate the duplicates? This way, you'll keep only exclusive combinations of "QF SEASONALITY", "CXR", and "DEST".

[D_Marcel]

If you really need to use VBA to this demand, instead of formulae or excel features, you can define a Dictionary.

[VBA]Dim Dict as Object
Dim MyIndex as Long

Set Dict = CreateObject("Scripting.Dictionary")
Dict.CompareMode = vbTextCompare

For I = 1 to MyLastRow
Key = Cells(1,1).value & Cells(1,2).value & Cells(1,3).value
If Not Dict.Exists(Key) Then
MyIndex = MyIndex +1
Dict.Add Key, MyIndex
End If

Next I

MyArray = Dict.Keys[/VBA]

And then unload your array into the cells.

[albe.btz]

I did not think about an excel formulated solution... let me see if it's viable..

[albe.btz]

The excel formulated is complicated because the Expanded Period table is VBA generated and its size changes from time to time, so i'd rather go for a VBA solution.

Maybe i was unclear: what this module should do is explained in this pictures

Capture.jpg

From EXPANDED PERIODS (pic above) the module have to select a Seaonality:SHO, LOW for Y class and the lowest J class for every CXR for every destination and copy those rows into this other sheet:

Capture2.JPG

where these rows are added at the end with the date of addition. So the difference between the Expanded Periods and Variations sheets is that the first is deleted and re-processed every time whilst Variations builds an historical series.

Let me know if you got it