Dynamic Rename Columns and sum data

[DeborahA]

Hi Eduardo,

I'm not exactly sure what you are trying to achieve. You want the dates to match the weeks? I see here that you have added columns with the dates. Are you trying to fill in all the dates between 11/18/2019 and 12/2 for example, and then count the number of weeks between both?

Thanks,

Deborah

[Edu]

Hi @DeborahA ,

Correct, and divide the difference of weeks between both by the quantities (not considering columns names). The date always starts on Mondays. Please see below input and desired output:

Input:

RecordID	DATE WK 1	DATE WK 2	DATE WKS 3-5	DATE WKS 6-10	DATE WKS 11-14	DATE WK 15	Qty Week 1	Qty Week 2	Qty Weeks 3-5	Qty Weeks 6-10	Qty Weeks 11-14	Qty Week 15
1	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	5600	5600	16800	16800	16800	5600
2	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	5400	5400	0	5400
3	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	3786	0	0
4	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	3360	3840	13440	14400	10080	3840
5	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	0	96
6	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	6880	6880	6880	0	6880
7	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6880	13760	34400	34400	34400	13760
8	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6400	6400	12800	19200	12800	6400
9	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	36000	0
10	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	11200	0	11200	11200	11200	0

Desired Output: 

RecordID	DATE WK 1	DATE WK 2	DATE WK 3	DATE WK 4	DATE WK 5	DATE WK 6	DATE WK 7	DATE WK 8	DATE WK 9	DATE WK 10	Qty Week 1	Qty Week 2	Qty Week 3	Qty Week 4	Qty Week 5	Qty Week 6	Qty Week 7	Qty Week 8	Qty Week 9	Qty Week 10
1	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	5600	5600	8400	8400	4200	4200	4200	4200	16800	5600
2	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	2700	2700	1350	1350	1350	1350	0	5400
3	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	946.5	946.5	946.5	946.5	0	0
4	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	3360	3840	6720	6720	3600	3600	3600	3600	10080	3840
5	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	0	96
6	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	6880	3440	3440	1720	1720	1720	1720	0	6880
7	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6880	13760	17200	17200	8600	8600	8600	8600	34400	13760
8	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6400	6400	6400	6400	4800	4800	4800	4800	12800	6400
9	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	36000	0
10	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	11200	0	5600	5600	2800	2800	2800	2800	11200	0

Thank you!! 🙂

Eduardo

[Edu]

Hi @DeborahA ,

Correct, and then divide the quantities by the number of weeks between both dates (consider dates instead of columns names). Please see new input and desired output:

Input:

RecordID	DATE WK 1	DATE WK 2	DATE WKS 3-5	DATE WKS 6-10	DATE WKS 11-14	DATE WK 15	Qty Week 1	Qty Week 2	Qty Weeks 3-5	Qty Weeks 6-10	Qty Weeks 11-14	Qty Week 15
1	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	5600	5600	16800	16800	16800	5600
2	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	5400	5400	0	5400
3	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	3786	0	0
4	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	3360	3840	13440	14400	10080	3840
5	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	0	96
6	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	6880	6880	6880	0	6880
7	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6880	13760	34400	34400	34400	13760
8	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6400	6400	12800	19200	12800	6400
9	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	36000	0
10	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	11200	0	11200	11200	11200	0

Desired Output:

DATE WK 1	DATE WK 2	DATE WK 3	DATE WK 4	DATE WK 5	DATE WK 6	DATE WK 7	DATE WK 8	DATE WK 9	DATE WK 10	Qty Week 1	Qty Week 2	Qty Week 3	Qty Week 4	Qty Week 5	Qty Week 6	Qty Week 7	Qty Week 8	Qty Week 9	Qty Week 10
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	5600	5600	8400	8400	4200	4200	4200	4200	16800	5600
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	2700	2700	1350	1350	1350	1350	0	5400
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	946.5	946.5	946.5	946.5	0	0
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	3360	3840	6720	6720	3600	3600	3600	3600	10080	3840
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	0	96
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	6880	3440	3440	1720	1720	1720	1720	0	6880
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6880	13760	17200	17200	8600	8600	8600	8600	34400	13760
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6400	6400	6400	6400	4800	4800	4800	4800	12800	6400
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	36000	0
11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	11200	0	5600	5600	2800	2800	2800	2800	11200	0

Thank you!! 🙂

Eduardo

[Edu]

Hi @DeborahA ,

Correct, and then divided the quantities of this column by the number of weeks (not considering the column names to do the calculations, but the results of this division). Please see below another input and desired output:

Input:

RecordID	DATE WK 1	DATE WK 2	DATE WKS 3-5	DATE WKS 6-10	DATE WKS 11-14	DATE WK 15	Qty Week 1	Qty Week 2	Qty Weeks 3-5	Qty Weeks 6-10	Qty Weeks 11-14	Qty Week 15
1	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	5600	5600	16800	16800	16800	5600
2	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	5400	5400	0	5400
3	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	3786	0	0
4	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	3360	3840	13440	14400	10080	3840
5	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	0	96
6	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	6880	6880	6880	0	6880
7	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6880	13760	34400	34400	34400	13760
8	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	6400	6400	12800	19200	12800	6400
9	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	0	0	0	0	36000	0
10	11/4/2019	11/11/2019	11/18/2019	12/2/2019	12/30/2019	1/6/2020	11200	0	11200	11200	11200	0

Desired Output:

RecordID	DATE WK 1	DATE WK 2	DATE WK 3	DATE WK 4	DATE WK 5	DATE WK 6	DATE WK 7	DATE WK 8	DATE WK 9	DATE WK 10	Qty Week 1	Qty Week 2	Qty Week 3	Qty Week 4	Qty Week 5	Qty Week 6	Qty Week 7	Qty Week 8	Qty Week 9	Qty Week 10
1	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	5600	5600	8400	8400	4200	4200	4200	4200	16800	5600
2	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	2700	2700	1350	1350	1350	1350	0	5400
3	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	946.5	946.5	946.5	946.5	0	0
4	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	3360	3840	6720	6720	3600	3600	3600	3600	10080	3840
5	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	0	96
6	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	6880	3440	3440	1720	1720	1720	1720	0	6880
7	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6880	13760	17200	17200	8600	8600	8600	8600	34400	13760
8	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	6400	6400	6400	6400	4800	4800	4800	4800	12800	6400
9	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	0	0	0	0	0	0	0	0	36000	0
10	11/4/2019	11/11/2019	11/18/2019	11/25/2019	12/2/2019	12/9/2019	12/16/2019	12/23/2019	12/30/2019	1/6/2020	11200	0	5600	5600	2800	2800	2800	2800	11200	0

Thank you! 

Best regards,

Eduardo