Looping and Combining two arrays

[Guinaba]

Hi guys,

Wondering if someone could help me out, I have two arrays (below) and I'd like to combine both in a third one using the following logic:

WkArr	UniqArr
WkArr(1,1) = "Week Commencing Date" WkArr(1,2) = #2/01/2021# WkArr(1,3) = #9/01/2021# WkArr(1,4) = #16/01/2021# WkArr(1,5) = #23/01/2021# WkArr(1,6) = #30/01/2021#	UniqArr(0)(0) = "Brand" UniqArr(0)(1) = "SKUID" UniqArr(0)(2) = "Color" UniqArr(0)(3) = "Shape" UniqArr(1) UniqArr(1)(0) = "Tonto" UniqArr(1)(1) = "45678" UniqArr(1)(2) = "Green" UniqArr(1)(3) = "Square" UniqArr(2) UniqArr(2)(0) = "Benq" UniqArr(2)(1) = "45698" UniqArr(2)(2) = "Blue" UniqArr(2)(3) = "Oval"

Final Result:

Week Commencing Date	Brand	SKUID	Color	Shape
2/01/2021	Tonto	45678​	Green	Square
9/01/2021	Tonto	45678​	Green	Square
16/01/2021	Tonto	45678​	Green	Square
23/01/2021	Tonto	45678​	Green	Square
30/01/2021	Tonto	45678​	Green	Square
2/01/2021	Benq	45698​	Blue	Oval
9/01/2021	Benq	45698​	Blue	Oval
16/01/2021	Benq	45698​	Blue	Oval
23/01/2021	Benq	45698​	Blue	Oval
30/01/2021	Benq	45698​	Blue	Oval

'Variant to hold the array element
   Dim Wk_Start As Variant
   
   'Array to keep combination between weeks and master data arrays
    Dim ComboArr() As Variant
    Dim r As Integer, c As Integer
    
   'Loop through the entire array
   For Each Wk_Start In WkArr
       For r = 2 To UBound(UniqArr)
           For c = 2 To UBound(UniqArr)
               ComboArr(r, c) = UniqArr(r, c)
           Next c
       Next r
   Next Wk_Start

 

[Norie]

Could you post an example of some 'real' data?

All I've seen so far is the data from the original post which I used to hard-code a solution and the data from post #5 which, to me anyway, doesn't appear to represent what you actually have.

 

[Guinaba]

Hi @Norie
That is the file structure (Picture A), just showing two items (111 and 222), the file is much bigger than this, in both dimenions (roles and col.). The columns grow to the right side every week with new dates and I can have new products as well. the Picture B is how I would like to convert it. I thought in breaking down in smaller chunks (arrays) would be easier to convert it to the new format.

My original idea was to have:

1) Array for time period (WW PromoWeek)
2) Array for Master Data Range (Col.2 to Col.5)
3) Array for Metrics Rows (Col.8) onwards including the metrics (Manassen Tactic,Manassen Promo Price,Manassen Save Per Unit,Manassen Case Deal, Manassen Scan Cases, etc)
And combine all of them.

Picture A

							Calendar Week	Wk 1	Wk 2	Wk 3	Wk 4
	Col.2	Col.3	Col.4	Col.5			WW Promo Week	2/01/2019	9/01/2019	16/01/2019	23/01/2019
View	Brand	MFA Ref	WOW Ref	Description	Source	Match/Index Column	Sales Event
1​	AA	111	123	Apaca	PROMO Grid	Tactic	Manassen Tactic
1​	AA	111	123	Apaca	PROMO Grid	Promo Price	Manassen Promo Price
2​	AA	111	123	Apaca	PROMO Grid	Save Per Unit	Manassen Save Per Unit
Unit Deal	AA	111	123	Apaca	PROMO Grid	Case Deal	Manassen Case Deal
1​	AA	111	123	Apaca	PROMO Grid	Scan Cases	Manassen Scan Cases
1​	AA	111	123	Apaca	PROMO Grid	Final Demand	Manassen Demand Fsct	3,048	2,388	2,184	2,268
1​	AA	111	123	Apaca	PROMO Grid	Baseline	Manassen Baseline	3,048	2,388	2,184	2,268
1​	AA	111	123	Apaca	PROMO Grid	Promo	Manassen Promo
1​	AA	111	123	Apaca			Manassen Ex DC Sales	3,072	1,920	3,342	2,646
2​	AA	111	123	Apaca	PROMO Grid	Issue	Manassen Review Flag
1​	AA	111	123	Apaca	Worklist	Worklist Tactics	Woolworths Tactics
1​	AA	111	123	Apaca	Worklist	Worklist Price	Woolworths Sell Price	$5.65	$5.64	$5.65	$5.58
1​	AA	111	123	Apaca	Worklist	Worklist Save Per Unit	Woolworths Save Per Unit
1​	AA	111	123	Apaca	Worklist	Worklist Forecast	Woolworths Scan Forecast	2,268	2,236	2,462	2,447
1​	AA	111	123	Apaca			Woolworths Projected Orders
1​	AA	111	123	Apaca			Woolworths Scan Actuals	2,215	2,198	2,206	2,381
1​	AA	111	123	Apaca			Woolworths Store Ranging	855	856	856	856
Serivce Lv	AA	111	123	Apaca			Woolworths DC Service Level	76%	100%	100%	100%
2​	AA	111	123	Apaca			Woolworths Avg Sell Price	$5.62	$5.62	$5.65	$5.56
1​	AB	222	321	Babaca	PROMO Grid	Tactic	Manassen Tactic
1​	AB	222	321	Babaca	PROMO Grid	Promo Price	Manassen Promo Price
2​	AB	222	321	Babaca	PROMO Grid	Save Per Unit	Manassen Save Per Unit
Unit Deal	AB	222	321	Babaca	PROMO Grid	Case Deal	Manassen Case Deal
1​	AB	222	321	Babaca	PROMO Grid	Scan Cases	Manassen Scan Cases
1​	AB	222	321	Babaca	PROMO Grid	Final Demand	Manassen Demand Fsct	3,048	2,388	2,184	2,268
1​	AB	222	321	Babaca	PROMO Grid	Baseline	Manassen Baseline	3,048	2,388	2,184	2,268
1​	AB	222	321	Babaca	PROMO Grid	Promo	Manassen Promo
1​	AB	222	321	Babaca			Manassen Ex DC Sales	3,072	1,920	3,342	2,646
2​	AB	222	321	Babaca	PROMO Grid	Issue	Manassen Review Flag
1​	AB	222	321	Babaca	Worklist	Worklist Tactics	Woolworths Tactics
1​	AB	222	321	Babaca	Worklist	Worklist Price	Woolworths Sell Price	$5.65	$5.64	$5.65	$5.58
1​	AB	222	321	Babaca	Worklist	Worklist Save Per Unit	Woolworths Save Per Unit
1​	AB	222	321	Babaca	Worklist	Worklist Forecast	Woolworths Scan Forecast	2,268	2,236	2,462	2,447
1​	AB	222	321	Babaca			Woolworths Projected Orders
1​	AB	222	321	Babaca			Woolworths Scan Actuals	2,215	2,198	2,206	2,381
1​	AB	222	321	Babaca			Woolworths Store Ranging	855	856	856	856
Serivce Lv	AB	222	321	Babaca			Woolworths DC Service Level	76%	100%	100%	100%
2​	AB	222	321	Babaca			Woolworths Avg Sell Price	$5.62	$5.62	$5.65	$5.56

Picture B

WW Promo Week

Brand

MFA Ref

WOW Ref

Description

Manassen Tactic

Manassen Promo Price

Manassen Save Per Unit

Manassen Case Deal

Manassen Scan Cases

Manassen Demand Fsct

Manassen Baseline

Manassen Promo

Manassen Ex DC Sales

Manassen Review Flag

Woolworths Tactics

Woolworths Sell Price

Woolworths Save Per Unit

Woolworths Scan Forecast

Woolworths Projected Orders

Woolworths Scan Actuals

Woolworths Store Ranging

Woolworths DC Service Level

Woolworths Avg Sell Price

2/01/2019

AA

111

123

Apaca

9/01/2019

AA

111

123

Apaca

16/01/2019

AA

111

123

Apaca

23/01/2019

AA

111

123

Apaca

2/01/2019

AB

222

321

Babaca

9/01/2019

AB

222

321

Babaca

16/01/2019

AB

222

321

Babaca

23/01/2019

AB

222

321

Babaca