I have data for over 1,500 items in the following format:
And want to convert it to this format:
Please note that the data in the second worksheet is in weeks and doesn't directly align with the date in the first worksheet. I would prefer for the quantities to be populated in the column of the date following the receipt date.
Any suggestions would be greatly appreciated!
Bit expanding that - create helper Calendar table with dates and related weeks, create relationship on dates between tables and pivot from data model.
Thank you @Haytham Amairah! I have considered using a pivot table and grouping the data but, for some reason, the version of Excel that I have only allows for the data to be grouped by month, quarter, and/or year. Also, I simplified the worksheet that I am using in the previous post. The receipt quantity is actually embedded in a larger set of data. It would be in Row 13 of the worksheet shown below (just one part number of the over 1,500 mentioned before). I was hoping there would be a way to do it without creating a pivot table but maybe that is wishful thinking.
