-1

I have a challenge on my hands to convert data from a monthly matrix to a linear weekly format.

The sample sheet is here - https://docs.google.com/spreadsheets/d/1q5TUsQh3LVo53kgLEGe9-ajzbMt1_CVYH1hoNl8kKAc/edit#gid=17593117

I have had some (actually a lot) of help from this forum previously on a similar task, so I have borrowed some of the formula logic from there.

Here's a synopsis of what is currently in the sheet -

The "monthly forecast" tab is the input data for this process. The "weekly forecast" is the required outcome. The "WeekMnth" tab provides -

  • The required date for the weekly buckets - column B
  • The relationship to the monthly date (these are fiscal months) column C
  • The factor to split the monthly number into weekly (as you will see it is a typical 445 week to fiscal month pattern) - column D

On the 'Testing' tab in cell A2 you will see my starting point for converting the monthly data. I have the Itemname and site values doing what I need them to.

Where I am currently stuck is on assigning the weekly date value in column C to each Itemname/site combination. Hopefully someone can offer some direction on overcoming this roadblock.

Once I have the week dates assigned, I think I can figure out the weekly quantity via lookups OK.

Ilya Kern
  • 89
  • 1
  • 4
  • 14
  • Possible duplicate of [How do you create a "reverse pivot" in Google Sheets?](http://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets) – Rubén Oct 29 '16 at 04:04

1 Answers1

0

I managed to reach a solution that 'works'. It's in the Testing tab on the sheet I shared in the first post if anyone's interested.

Also, if anyone has a more elegant solution, I'm still keen to clean up my approach.

Ilya Kern
  • 89
  • 1
  • 4
  • 14