VBA Loop for each to build forecast

[natekris8183]

I have a data set that includes Column1 (Dates, week-to-week starting on Mondays), Column2 (product by SKU), Columns3 (product status, active... this will eventually be left out as soon as I finish the VBA to permit a list to be built allowing only active products to be selected in the data validation drop down), Column4 (Source: legacy or dealer). The SKUs are unique to the products obviously. I need to create a list range of data set to input into the Worksheet Function Trend based upon dates prior to the current date in the table range (RawSalesDataTbl[@Week]). This will provide a running trend to juxtapose against our proposed forecast. The complication is the data set is not linear thus I cannot simple name a range of "y" values and "x" values. The easy one is naming the proposed "X" value as it will be the next week value, again determined at RawSalesDataTbl[@Week].

 

[michaelsmith559]

Can you post an example sheet or actual sheet with what you want to forecast?

 

[natekris8183]

Unfortunately my profile in the Forum does not permit me to upload files. I'd be happy to furnish a sample copy if anyone would be willing to provide an email. It's only a test data set with fictitious SKU names. The layout is the same as the live raw data set. If someone knows how I can request changes to my posting permissions I would happily post.

 

[natekris8183]

I did attempt the following two variations in hopes of creating an array. The complication is that the known x's are carried in but the False values generate null values as "0" and thus if the occurrences of the x's are 27 for SKU1 but there are 330 SKUs, the other 329 non-SKU1 values generate a "0" value there increasing the instances of X and incorrectly TREND the new X value.

=TREND(([SKU]=[@SKU])*[Actual],([SKU]=[@SKU])*([Week]<[@Week])*[Week],[@Week])

The other was the same with an IF statement array matching the SKU column to the current SKU value at that row and the same thing for the Y values of the known weeks based on the same array IF statement.

 

[michaelsmith559]

You will need to download mrexcelhtml or excel genie. With one of these, you will be able to post an example worksheet.

 

[michaelsmith559]

If you go to the main page of this site, click on excel questions, the very top post says recommended add-ins. Click on this and under recommended add-ins look for mrexcelhtml or excel genie.

 

[natekris8183]

I only took just a few sample lines. There are over 17,000 so this is a segment of fabricated data as a sample.

Excel 2012
	A	B	C	D	E	F	G
1	Week	SKU	SKU Status	Source	Actual	Trend	Forecast
2	1/12/15	SKU10	Active	Dealer	10
3	1/19/15	SKU10	Active	Dealer	8
4	2/2/15	SKU10	Active	Dealer	7
5	2/16/15	SKU10	Active	Dealer	7
6	3/2/15	SKU10	Active	Dealer	5
7	3/23/15	SKU10	Active	TCC	1
8	4/6/15	SKU10	Active	Dealer	3
9	4/13/15	SKU10	Active	TCC	1
10	4/27/15	SKU10	Active	Dealer	10
11	6/1/15	SKU10	Active	Dealer	3
12	6/22/15	SKU10	Active	Dealer	6
13	6/22/15	SKU10	Active	TCC	1
14	6/29/15	SKU10	Active	TCC	3
15	7/6/15	SKU10	Active	Dealer	21
16	8/3/15	SKU10	Active	Dealer	11
17	8/10/15	SKU10	Active	Dealer	5
18	8/17/15	SKU10	Active	Dealer	5
19	8/31/15	SKU10	Active	Dealer	10
20	8/31/15	SKU100	Active	TCC	0		50
21	8/31/15	SKU101	Active	TCC	0		57
22	8/31/15	SKU102	Active	TCC	0		34
23	8/31/15	SKU103	Active	TCC	0		49
24	8/31/15	SKU104	Active	TCC	0		277
25	8/31/15	SKU105	Active	TCC	0		172
26	8/31/15	SKU106	Active	TCC	0		170
27	8/31/15	SKU107	Active	TCC	0		175
28	8/31/15	SKU108	Active	TCC	0		520
29	8/31/15	SKU109	Active	TCC	0		3
30	9/7/15	SKU100	Active	TCC	0		58
31	9/7/15	SKU101	Active	TCC	0		66
32	9/7/15	SKU102	Active	TCC	0		40
33	9/7/15	SKU103	Active	TCC	0		57
34	9/7/15	SKU104	Active	TCC	0		323
35	9/7/15	SKU105	Active	TCC	0		200
36	9/7/15	SKU106	Active	TCC	0		198
37	9/7/15	SKU107	Active	TCC	0		204
38	9/7/15	SKU108	Active	TCC	0		607
39	9/7/15	SKU109	Active	TCC	0		4
40	9/14/15	SKU100	Active	TCC	0		58
41	9/14/15	SKU101	Active	TCC	0		66
42	9/14/15	SKU102	Active	TCC	0		40
43	9/14/15	SKU103	Active	TCC	0		57
44	9/14/15	SKU104	Active	TCC	0		323
45	9/14/15	SKU105	Active	TCC	0		200
46	9/14/15	SKU106	Active	TCC	0		198
47	9/14/15	SKU107	Active	TCC	0		204
48	9/14/15	SKU108	Active	TCC	0		607
49	9/14/15	SKU109	Active	TCC	0		4
50	9/21/15	SKU10	Active	Dealer	22
51	9/21/15	SKU100	Active	Dealer	325
52	9/21/15	SKU101	Active	Dealer	495
53	9/21/15	SKU102	Active	Dealer	458
54	9/21/15	SKU103	Active	Dealer	517
55	9/21/15	SKU104	Active	Dealer	868
56	9/21/15	SKU105	Active	Dealer	728
57	9/21/15	SKU106	Active	Dealer	701
58	9/21/15	SKU107	Active	Dealer	759
59	9/21/15	SKU108	Active	Dealer	2078
60	9/21/15	SKU109	Active	Dealer	13
61	9/21/15	SKU10	Active	TCC	2		0
62	9/21/15	SKU100	Active	TCC	179		58
63	9/21/15	SKU101	Active	TCC	213		66
64	9/21/15	SKU102	Active	TCC	122		40
65	9/21/15	SKU103	Active	TCC	173		57
66	9/21/15	SKU104	Active	TCC	1016		323
67	9/21/15	SKU105	Active	TCC	619		200
68	9/21/15	SKU106	Active	TCC	654		198
69	9/21/15	SKU107	Active	TCC	636		204
70	9/21/15	SKU108	Active	TCC	1792		607
71	9/21/15	SKU109	Active	TCC	12		4
72	9/28/15	SKU100	Active	Dealer	28
73	9/28/15	SKU101	Active	Dealer	55
74	9/28/15	SKU102	Active	Dealer	118
75	9/28/15	SKU103	Active	Dealer	54
76	9/28/15	SKU104	Active	Dealer	133
77	9/28/15	SKU105	Active	Dealer	55
78	9/28/15	SKU106	Active	Dealer	120
79	9/28/15	SKU107	Active	Dealer	45
80	9/28/15	SKU108	Active	Dealer	462
81	9/28/15	SKU109	Active	Dealer	14
82	9/28/15	SKU100	Active	TCC	157		59
83	9/28/15	SKU101	Active	TCC	135		67
84	9/28/15	SKU102	Active	TCC	146		41
85	9/28/15	SKU103	Active	TCC	131		58
86	9/28/15	SKU104	Active	TCC	546		330
87	9/28/15	SKU105	Active	TCC	725		205
88	9/28/15	SKU106	Active	TCC	271		202
89	9/28/15	SKU107	Active	TCC	729		208
90	9/28/15	SKU108	Active	TCC	2082		620
91	9/28/15	SKU109	Active	TCC	23		4
92	10/5/15	SKU10	Active	Dealer	10
93	10/5/15	SKU100	Active	Dealer	65
94	10/5/15	SKU101	Active	Dealer	111
95	10/5/15	SKU102	Active	Dealer	32
96	10/5/15	SKU103	Active	Dealer	79
97	10/5/15	SKU104	Active	Dealer	562
98	10/5/15	SKU105	Active	Dealer	99
99	10/5/15	SKU106	Active	Dealer	17
Raw Data

 

[michaelsmith559]

Would your boss allow you to make an example sheet to bring home, to use on your personal computer?

 

[natekris8183]

I was able to clip it out. The attached shows the sample. The aim is to get the trend of the associated SKU from the current row based on the date as the proposed "X" value with dates prior to that being the known "X" values and the values for "Y" would be the data in the Actual column associated by those dates less than the data associated with the current row. Hence why my previous function looked to build the array based on those values [Week]<[@Week]. The problem is that all False values generate a "0" in the array there by counting the "0".

 

[michaelsmith559]

What are the actual values for row 20 to row 49?