How to sort varying range of data into weekly buckets

[selenebean]

Hi all,

I'm trying to create essentially a demand spreadsheet. I want the customer to be able to input the item and location they plan to sell something, the start week of selling, how many total weeks they expect to sell the item, and the # of items they expect to sell in a week. I want to then take this data input, and create a table/sheet with the demand by week for each item/location pair. I'm having a hard time figuring out how to have Excel sort through a range of data inputs to find the right data, as well as then input that data for each week between the start and end date. The # of inputs will vary (i.e. there could be 12 lines of data or 200), and there will sometimes be seasonality for when the customer expects to sell items. Image attached which probably better explains what I'm trying to do.

Appreciate any help I can receive! I'm not opposed to having Excel automatically manipulate the customer data input to put it in an easier way for Excel to then input into the weekly chart.

• Excel Version: Office 365 but needs to be compatible with other versions of Excel (i.e. sadly no XLOOKUP and other 365 only functions)
• Excel Environment: desktop, Windows
• Your Knowledge Level: Intermediate?
• Open to Macros and other fancy tools, but must admit I've never used Power Query or Power BI. Needs to be able to be used by customer (AKA dummy proof, and any add-ons need to be free and easy to add to Excel)

 

[BSALV]

with a macro "unpack", 2 listobjects and 1 pivottable

Sub Unpack()
     Set dict = CreateObject("scripting.dictionary")
     With Sheets("selenebean")
          a = .ListObjects("TBL_Data").DataBodyRange.Value2
          For i = 1 To UBound(a)
               datum = CDbl(a(i, 3) - WorksheetFunction.Weekday(a(i, 3)) + 1)
               For j = 1 To a(i, 4)
                    d = datum + (j - 1) * 7
                    dict.Add dict.Count, Array(a(i, 1), a(i, 2), d, a(i, 6), Year(d), WorksheetFunction.WeekNum(d))
               Next j
          Next i

          With .ListObjects(2)
               If .ListRows.Count Then .DataBodyRange.Delete
               If dict.Count Then
                    a = Application.Index(dict.items, 0, 0)
                    .ListRows.Add.Range.Range("A1").Resize(UBound(a), UBound(a, 2)).Value = a
               End If
          End With
     End With

     ThisWorkbook.RefreshAll
End Sub

amoverton2 - 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	store	item	startweek	# weeks	End week	weekly demand	Startweeknumber	Endweeknumber		store	item	Sunday	demand	year	week2
2	Denver	purse	01/01/22	2	01/15/22	2	1	3		Denver	purse	12/26/21	2	2021	53		Som van demand			item	store
3	denver	purse	01/01/23	2	01/15/23	2	1	3		Denver	purse	01/02/22	2	2022	2					purse		Totaal purse	backpack		Totaal backpack	beanie		Totaal beanie	Eindtotaal
4	LA	purse	03/01/22	2	03/15/22	2	10	12		denver	purse	01/01/23	2	2023	1		year	week2	Sunday	Denver	LA		Denver	LA		Denver	LA
5	LA	purse	08/01/22	2	08/15/22	2	32	34		denver	purse	01/08/23	2	2023	2		2021	53	12/26/21	2		2	2	2	4				6
6	LA	purse	01/01/23	2	01/15/23	2	1	3		LA	purse	02/27/22	2	2022	10
7	denver	backpack	01/01/22	2	01/15/22	2	1	3		LA	purse	03/06/22	2	2022	11		2022	2	01/02/22	2		2	2	2	4				6
8	la	backpack	01/01/22	2	01/15/22	2	1	3		LA	purse	07/31/22	2	2022	32		2022	10	02/27/22		2	2							2
9	denver	beanie	10/01/22	2	10/15/22	2	40	42		LA	purse	08/07/22	2	2022	33		2022	11	03/06/22		2	2							2
10	la	beanie	10/01/22	2	10/15/22	2	40	42		LA	purse	01/01/23	2	2023	1		2022	32	07/31/22		2	2							2
11										LA	purse	01/08/23	2	2023	2		2022	33	08/07/22		2	2							2
12										denver	backpack	12/26/21	2	2021	53		2022	40	09/25/22							2	2	4	4
13										denver	backpack	01/02/22	2	2022	2		2022	41	10/02/22							2	2	4	4
14										la	backpack	12/26/21	2	2021	53
15										la	backpack	01/02/22	2	2022	2		2023	1	01/01/23	2	2	4							4
16										denver	beanie	09/25/22	2	2022	40		2023	2	01/08/23	2	2	4							4
17										denver	beanie	10/02/22	2	2022	41
18										la	beanie	09/25/22	2	2022	40		Eindtotaal			8	12	20	4	4	8	4	4	8	36
19										la	beanie	10/02/22	2	2022	41
20
21
22
23
24
25
Selenebean

Cell Formulas
Range	Formula
G2:G10	G2	=WEEKNUM([@startweek])
H2:H10	H2	=WEEKNUM([@[End week]])
E2:E10	E2	=+[@startweek]+[@['# weeks]]*7