Looping and Combining two arrays

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
101
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:

WkArrUniqArr
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 DateBrandSKUIDColorShape
2/01/2021Tonto
45678​
GreenSquare
9/01/2021Tonto
45678​
GreenSquare
16/01/2021Tonto
45678​
GreenSquare
23/01/2021Tonto
45678​
GreenSquare
30/01/2021Tonto
45678​
GreenSquare
2/01/2021Benq
45698​
BlueOval
9/01/2021Benq
45698​
BlueOval
16/01/2021Benq
45698​
BlueOval
23/01/2021Benq
45698​
BlueOval
30/01/2021Benq
45698​
BlueOval

VBA Code:
'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

Well-known Member
Joined
Apr 28, 2004
Messages
76,298
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
101
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 WeekWk 1Wk 2Wk 3Wk 4
Col.2Col.3Col.4Col.5WW Promo Week2/01/20199/01/201916/01/201923/01/2019
ViewBrandMFA RefWOW RefDescriptionSourceMatch/Index ColumnSales Event
1​
AA111123ApacaPROMO GridTacticManassen Tactic
1​
AA111123ApacaPROMO GridPromo PriceManassen Promo Price
2​
AA111123ApacaPROMO GridSave Per UnitManassen Save Per Unit
Unit DealAA111123ApacaPROMO GridCase DealManassen Case Deal
1​
AA111123ApacaPROMO GridScan CasesManassen Scan Cases
1​
AA111123ApacaPROMO GridFinal DemandManassen Demand Fsct3,0482,3882,1842,268
1​
AA111123ApacaPROMO GridBaselineManassen Baseline3,0482,3882,1842,268
1​
AA111123ApacaPROMO GridPromoManassen Promo
1​
AA111123ApacaManassen Ex DC Sales3,0721,9203,3422,646
2​
AA111123ApacaPROMO GridIssueManassen Review Flag
1​
AA111123ApacaWorklistWorklist TacticsWoolworths Tactics
1​
AA111123ApacaWorklistWorklist PriceWoolworths Sell Price$5.65$5.64$5.65$5.58
1​
AA111123ApacaWorklistWorklist Save Per UnitWoolworths Save Per Unit
1​
AA111123ApacaWorklistWorklist ForecastWoolworths Scan Forecast2,2682,2362,4622,447
1​
AA111123ApacaWoolworths Projected Orders
1​
AA111123ApacaWoolworths Scan Actuals2,2152,1982,2062,381
1​
AA111123ApacaWoolworths Store Ranging855856856856
Serivce LvAA111123ApacaWoolworths DC Service Level76%100%100%100%
2​
AA111123ApacaWoolworths Avg Sell Price$5.62$5.62$5.65$5.56
1​
AB222321BabacaPROMO GridTacticManassen Tactic
1​
AB222321BabacaPROMO GridPromo PriceManassen Promo Price
2​
AB222321BabacaPROMO GridSave Per UnitManassen Save Per Unit
Unit DealAB222321BabacaPROMO GridCase DealManassen Case Deal
1​
AB222321BabacaPROMO GridScan CasesManassen Scan Cases
1​
AB222321BabacaPROMO GridFinal DemandManassen Demand Fsct3,0482,3882,1842,268
1​
AB222321BabacaPROMO GridBaselineManassen Baseline3,0482,3882,1842,268
1​
AB222321BabacaPROMO GridPromoManassen Promo
1​
AB222321BabacaManassen Ex DC Sales3,0721,9203,3422,646
2​
AB222321BabacaPROMO GridIssueManassen Review Flag
1​
AB222321BabacaWorklistWorklist TacticsWoolworths Tactics
1​
AB222321BabacaWorklistWorklist PriceWoolworths Sell Price$5.65$5.64$5.65$5.58
1​
AB222321BabacaWorklistWorklist Save Per UnitWoolworths Save Per Unit
1​
AB222321BabacaWorklistWorklist ForecastWoolworths Scan Forecast2,2682,2362,4622,447
1​
AB222321BabacaWoolworths Projected Orders
1​
AB222321BabacaWoolworths Scan Actuals2,2152,1982,2062,381
1​
AB222321BabacaWoolworths Store Ranging855856856856
Serivce LvAB222321BabacaWoolworths DC Service Level76%100%100%100%
2​
AB222321BabacaWoolworths Avg Sell Price$5.62$5.62$5.65$5.56

Picture B

WW Promo WeekBrandMFA RefWOW RefDescriptionManassen TacticManassen Promo PriceManassen Save Per UnitManassen Case DealManassen Scan CasesManassen Demand FsctManassen BaselineManassen PromoManassen Ex DC SalesManassen Review FlagWoolworths TacticsWoolworths Sell PriceWoolworths Save Per UnitWoolworths Scan ForecastWoolworths Projected OrdersWoolworths Scan ActualsWoolworths Store RangingWoolworths DC Service LevelWoolworths Avg Sell Price
2/01/2019AA111123Apaca
9/01/2019AA111123Apaca
16/01/2019AA111123Apaca
23/01/2019AA111123Apaca
2/01/2019AB222321Babaca
9/01/2019AB222321Babaca
16/01/2019AB222321Babaca
23/01/2019AB222321Babaca
 

Watch MrExcel Video

Forum statistics

Threads
1,133,270
Messages
5,657,753
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top