Aligning of Dates from 2 different sources

[gmooney]

Having an issue with the following table that is bringing in data from 2 different sources and whose dates do not line up to the same date so I have to apply "some logic" to try to get them aligned.

Column B is a start date from one source while column D is data from another data source. As you can see in this example I found a total of 39 start dates in column D that I have to match up across the 52 weeks of start dates in column B. For purposes of this discussion I MANUALLY moved column D values to the correct row in order to match up to column B.

I don't want to MANUALLY move this each time I refresh these reports.

Also note in cell C27 I had to MANUALLY override the formula that was there before (which was F26-7) in order to account for the fact that column E changed from Wednesday to Sunday for that week.

Any thoughts or help would be greatly appreciated...I have been working on this for 2 weeks now trying to resolve.

Excel 2010
	A	B	C	D	E	F
1	Week	IRI START DATE (Always MONDAY)	IRI END DATE (Always SUNDAY)	Kroger ATL	AD Break Day of Week	AD START DATE
2	1	August 18, 2014	Aug 24, 2014	August 20, 2014	Wednesday	August 20, 2014
3	2	August 11, 2014	Aug 17, 2014	August 13, 2014	Wednesday	August 13, 2014
4	3	August 4, 2014	Aug 10, 2014	August 6, 2014	Wednesday	August 6, 2014
5	4	July 28, 2014	Aug 3, 2014	July 30, 2014	Wednesday	July 30, 2014
6	5	July 21, 2014	Jul 27, 2014	July 23, 2014	Wednesday	July 23, 2014
7	6	July 14, 2014	Jul 20, 2014	July 16, 2014	Wednesday	July 16, 2014
8	7	July 7, 2014	Jul 13, 2014	July 9, 2014	Wednesday	July 9, 2014
9	8	June 30, 2014	Jul 6, 2014	July 2, 2014	Wednesday	July 2, 2014
10	9	June 23, 2014	Jun 29, 2014	June 25, 2014	Wednesday	June 25, 2014
11	10	June 16, 2014	Jun 22, 2014	June 18, 2014	Wednesday	June 18, 2014
12	11	June 9, 2014	Jun 15, 2014	June 11, 2014	Wednesday	June 11, 2014
13	12	June 2, 2014	Jun 8, 2014	June 4, 2014	Wednesday	June 4, 2014
14	13	May 26, 2014	Jun 1, 2014	May 28, 2014	Wednesday	May 28, 2014
15	14	May 19, 2014	May 25, 2014	May 21, 2014	Wednesday	May 21, 2014
16	15	May 12, 2014	May 18, 2014	May 14, 2014	Wednesday	May 14, 2014
17	16	May 5, 2014	May 11, 2014	May 7, 2014	Wednesday	May 7, 2014
18	17	April 28, 2014	May 4, 2014	April 30, 2014	Wednesday	April 30, 2014
19	18	April 21, 2014	Apr 27, 2014	April 23, 2014	Wednesday	April 23, 2014
20	19	April 14, 2014	Apr 20, 2014	April 16, 2014	Wednesday	April 16, 2014
21	20	April 7, 2014	Apr 13, 2014	April 9, 2014	Wednesday	April 9, 2014
22	21	March 31, 2014	Apr 6, 2014			April 2, 2014
23	22	March 24, 2014	Mar 30, 2014	March 26, 2014	Wednesday	March 26, 2014
24	23	March 17, 2014	Mar 23, 2014	March 19, 2014	Wednesday	March 19, 2014
25	24	March 10, 2014	Mar 16, 2014	March 12, 2014	Wednesday	March 12, 2014
26	25	March 3, 2014	Mar 9, 2014	March 5, 2014	Wednesday	March 5, 2014
27	26	February 24, 2014	Mar 2, 2014	February 23, 2014	Sunday	February 23, 2014
28	27	February 17, 2014	Feb 23, 2014			February 16, 2014
29	28	February 10, 2014	Feb 16, 2014	February 9, 2014	Sunday	February 9, 2014
30	29	February 3, 2014	Feb 9, 2014	February 2, 2014	Sunday	February 2, 2014
31	30	January 27, 2014	Feb 2, 2014	January 26, 2014	Sunday	January 26, 2014
32	31	January 20, 2014	Jan 26, 2014			January 19, 2014
33	32	January 13, 2014	Jan 19, 2014	January 12, 2014	Sunday	January 12, 2014
34	33	January 6, 2014	Jan 12, 2014	January 5, 2014	Sunday	January 5, 2014
35	34	December 30, 2013	Jan 5, 2014	December 29, 2013	Sunday	December 29, 2013
36	35	December 23, 2013	Dec 29, 2013			December 22, 2013
37	36	December 16, 2013	Dec 22, 2013	December 15, 2013	Sunday	December 15, 2013
38	37	December 9, 2013	Dec 15, 2013			December 8, 2013
39	38	December 2, 2013	Dec 8, 2013			December 1, 2013
40	39	November 25, 2013	Dec 1, 2013			November 24, 2013
41	40	November 18, 2013	Nov 24, 2013	November 17, 2013	Sunday	November 17, 2013
42	41	November 11, 2013	Nov 17, 2013	November 10, 2013	Sunday	November 10, 2013
43	42	November 4, 2013	Nov 10, 2013			November 3, 2013
44	43	October 28, 2013	Nov 3, 2013	October 27, 2013	Sunday	October 27, 2013
45	44	October 21, 2013	Oct 27, 2013	October 20, 2013	Sunday	October 20, 2013
46	45	October 14, 2013	Oct 20, 2013	October 13, 2013	Sunday	October 13, 2013
47	46	October 7, 2013	Oct 13, 2013	October 6, 2013	Sunday	October 6, 2013
48	47	September 30, 2013	Oct 6, 2013	September 29, 2013	Sunday	September 29, 2013
49	48	September 23, 2013	Sep 29, 2013			September 22, 2013
50	49	September 16, 2013	Sep 22, 2013			September 15, 2013
51	50	September 9, 2013	Sep 15, 2013			September 8, 2013
52	51	September 2, 2013	Sep 8, 2013			September 1, 2013
53	52	August 26, 2013	Sep 1, 2013			August 25, 2013
AD CODING

Cell Formulas
Range	Formula
D1	=DATA!AO3
D2	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A1))),"")}
D3	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A2))),"")}
D4	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A3))),"")}
D5	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A4))),"")}
D6	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A5))),"")}
D7	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A6))),"")}
D8	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A7))),"")}
D9	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A8))),"")}
D10	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A9))),"")}
D11	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A10))),"")}
D12	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A11))),"")}
D13	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A12))),"")}
D14	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A13))),"")}
D15	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A14))),"")}
D16	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A15))),"")}
D17	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A16))),"")}
D18	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A17))),"")}
D19	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A18))),"")}
D20	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A19))),"")}
D21	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A20))),"")}
D23	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A21))),"")}
D24	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A22))),"")}
D25	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A23))),"")}
D26	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A24))),"")}
D27	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A25))),"")}
D29	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A26))),"")}
D30	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A27))),"")}
D31	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A28))),"")}
D33	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A29))),"")}
D34	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A30))),"")}
D35	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A31))),"")}
D37	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A32))),"")}
D41	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A33))),"")}
D42	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A34))),"")}
D44	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A35))),"")}
D45	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A36))),"")}
D46	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A37))),"")}
D47	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A38))),"")}
D48	{=IFERROR(INDEX('ECRM ADs'!$C$2:$C$1518,SMALL(IF(FREQUENCY(IF('ECRM ADs'!$E$2:$E$1518=$D$1,MATCH('ECRM ADs'!$C$2:$C$1518,'ECRM ADs'!$C$2:$C$1518,0)),ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1)>0,ROW('ECRM ADs'!$C$2:$C$1518)-ROW('ECRM ADs'!$C$2)+1),ROW(A39))),"")}
E23	=TEXT(WEEKDAY(D23,1),"DDDD")
E24	=TEXT(WEEKDAY(D24,1),"DDDD")
E25	=TEXT(WEEKDAY(D25,1),"DDDD")
E26	=TEXT(WEEKDAY(D26,1),"DDDD")
E27	=TEXT(WEEKDAY(D27,1),"DDDD")
E29	=TEXT(WEEKDAY(D29,1),"DDDD")
E30	=TEXT(WEEKDAY(D30,1),"DDDD")
E31	=TEXT(WEEKDAY(D31,1),"DDDD")
E33	=TEXT(WEEKDAY(D33,1),"DDDD")
E34	=TEXT(WEEKDAY(D34,1),"DDDD")
E35	=TEXT(WEEKDAY(D35,1),"DDDD")
E37	=TEXT(WEEKDAY(D37,1),"DDDD")
E41	=TEXT(WEEKDAY(D41,1),"DDDD")
E42	=TEXT(WEEKDAY(D42,1),"DDDD")
E44	=TEXT(WEEKDAY(D44,1),"DDDD")
E45	=TEXT(WEEKDAY(D45,1),"DDDD")
E46	=TEXT(WEEKDAY(D46,1),"DDDD")
E47	=TEXT(WEEKDAY(D47,1),"DDDD")
E48	=TEXT(WEEKDAY(D48,1),"DDDD")
E2	=TEXT(WEEKDAY(D2,1),"DDDD")
E3	=TEXT(WEEKDAY(D3,1),"DDDD")
E4	=TEXT(WEEKDAY(D4,1),"DDDD")
E5	=TEXT(WEEKDAY(D5,1),"DDDD")
E6	=TEXT(WEEKDAY(D6,1),"DDDD")
E7	=TEXT(WEEKDAY(D7,1),"DDDD")
E8	=TEXT(WEEKDAY(D8,1),"DDDD")
E9	=TEXT(WEEKDAY(D9,1),"DDDD")
E10	=TEXT(WEEKDAY(D10,1),"DDDD")
E11	=TEXT(WEEKDAY(D11,1),"DDDD")
E12	=TEXT(WEEKDAY(D12,1),"DDDD")
E13	=TEXT(WEEKDAY(D13,1),"DDDD")
E14	=TEXT(WEEKDAY(D14,1),"DDDD")
E15	=TEXT(WEEKDAY(D15,1),"DDDD")
E16	=TEXT(WEEKDAY(D16,1),"DDDD")
E17	=TEXT(WEEKDAY(D17,1),"DDDD")
E18	=TEXT(WEEKDAY(D18,1),"DDDD")
E19	=TEXT(WEEKDAY(D19,1),"DDDD")
E20	=TEXT(WEEKDAY(D20,1),"DDDD")
E21	=TEXT(WEEKDAY(D21,1),"DDDD")
B2	=C2-6
B3	=C3-6
B4	=C4-6
B5	=C5-6
B6	=C6-6
B7	=C7-6
B8	=C8-6
B9	=C9-6
B10	=C10-6
B11	=C11-6
B12	=C12-6
B13	=C13-6
B14	=C14-6
B15	=C15-6
B16	=C16-6
B17	=C17-6
B18	=C18-6
B19	=C19-6
B20	=C20-6
B21	=C21-6
B22	=C22-6
B23	=C23-6
B24	=C24-6
B25	=C25-6
B26	=C26-6
B27	=C27-6
B28	=C28-6
B29	=C29-6
B30	=C30-6
B31	=C31-6
B32	=C32-6
B33	=C33-6
B34	=C34-6
B35	=C35-6
B36	=C36-6
B37	=C37-6
B38	=C38-6
B39	=C39-6
B40	=C40-6
B41	=C41-6
B42	=C42-6
B43	=C43-6
B44	=C44-6
B45	=C45-6
B46	=C46-6
B47	=C47-6
B48	=C48-6
B49	=C49-6
B50	=C50-6
B51	=C51-6
B52	=C52-6
B53	=C53-6
C2	=DATA!BJ3
C3	=DATA!BJ4
C4	=DATA!BJ5
C5	=DATA!BJ6
C6	=DATA!BJ7
C7	=DATA!BJ8
C8	=DATA!BJ9
C9	=DATA!BJ10
C10	=DATA!BJ11
C11	=DATA!BJ12
C12	=DATA!BJ13
C13	=DATA!BJ14
C14	=DATA!BJ15
C15	=DATA!BJ16
C16	=DATA!BJ17
C17	=DATA!BJ18
C18	=DATA!BJ19
C19	=DATA!BJ20
C20	=DATA!BJ21
C21	=DATA!BJ22
C22	=DATA!BJ23
C23	=DATA!BJ24
C24	=DATA!BJ25
C25	=DATA!BJ26
C26	=DATA!BJ27
C27	=DATA!BJ28
C28	=DATA!BJ29
C29	=DATA!BJ30
C30	=DATA!BJ31
C31	=DATA!BJ32
C32	=DATA!BJ33
C33	=DATA!BJ34
C34	=DATA!BJ35
C35	=DATA!BJ36
C36	=DATA!BJ37
C37	=DATA!BJ38
C38	=DATA!BJ39
C39	=DATA!BJ40
C40	=DATA!BJ41
C41	=DATA!BJ42
C42	=DATA!BJ43
C43	=DATA!BJ44
C44	=DATA!BJ45
C45	=DATA!BJ46
C46	=DATA!BJ47
C47	=DATA!BJ48
C48	=DATA!BJ49
C49	=DATA!BJ50
C50	=DATA!BJ51
C51	=DATA!BJ52
C52	=DATA!BJ53
C53	=DATA!BJ54
F2	=C2-Q7
F3	=F2-7
F4	=F3-7
F5	=F4-7
F6	=F5-7
F7	=F6-7
F8	=F7-7
F9	=F8-7
F10	=F9-7
F11	=F10-7
F12	=F11-7
F13	=F12-7
F14	=F13-7
F15	=F14-7
F16	=F15-7
F17	=F16-7
F18	=F17-7
F19	=F18-7
F20	=F19-7
F21	=F20-7
F22	=F21-7
F23	=F22-7
F24	=F23-7
F25	=F24-7
F26	=F25-7
F27	=D27
F28	=F27-7
F29	=F28-7
F30	=F29-7
F31	=F30-7
F32	=F31-7
F33	=F32-7
F34	=F33-7
F35	=F34-7
F36	=F35-7
F37	=F36-7
F38	=F37-7
F39	=F38-7
F40	=F39-7
F41	=F40-7
F42	=F41-7
F43	=F42-7
F44	=F43-7
F45	=F44-7
F46	=F45-7
F47	=F46-7
F48	=F47-7
F49	=F48-7
F50	=F49-7
F51	=F50-7
F52	=F51-7
F53	=F52-7
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
DATA	=DATA!$A$3:$AI$61000

 

[RickXL]

Hi,

I am not entirely sure I understand the problem but ...

It looks as if you need to add a new column to the input sheets to work out the week number. =WEEKNUM will do that and you can choose different days for the start of the week. Then, I think you could match up the dates by using the week number.

For the C27 problem, you need to change the formula to incorporate the break day. Can you base the value in F on the one in D unless the one in D is blank? If itt is blank then do what you do now. For example:

=if(D27<>"", D27, F26-7)

 

[shg]

There's a workbook at https://app.box.com/shared/elrnbidnr7 with a macro and several examples.

 

[gmooney]

Thanks shg......Not sure what to do with this after reviewing it?

 

[shg]

Starting from this, ...

Row\Col	B​	C​	D​
1​	IRI Start​	IRI End​	Kroger ATL​
2​	18-Aug-2014​	24-Aug-2014​	20-Aug-2014​
3​	11-Aug-2014​	17-Aug-2014​	13-Aug-2014​
4​	04-Aug-2014​	10-Aug-2014​	06-Aug-2014​
5​	28-Jul-2014​	03-Aug-2014​	30-Jul-2014​
6​	21-Jul-2014​	27-Jul-2014​	23-Jul-2014​
7​	14-Jul-2014​	20-Jul-2014​	16-Jul-2014​
8​	07-Jul-2014​	13-Jul-2014​	09-Jul-2014​
9​	30-Jun-2014​	06-Jul-2014​	02-Jul-2014​
10​	23-Jun-2014​	29-Jun-2014​	25-Jun-2014​
11​	16-Jun-2014​	22-Jun-2014​	18-Jun-2014​
12​	09-Jun-2014​	15-Jun-2014​	11-Jun-2014​
13​	02-Jun-2014​	08-Jun-2014​	04-Jun-2014​
14​	26-May-2014​	01-Jun-2014​	28-May-2014​
15​	19-May-2014​	25-May-2014​	21-May-2014​
16​	12-May-2014​	18-May-2014​	14-May-2014​
17​	05-May-2014​	11-May-2014​	07-May-2014​
18​	28-Apr-2014​	04-May-2014​	30-Apr-2014​
19​	21-Apr-2014​	27-Apr-2014​	23-Apr-2014​
20​	14-Apr-2014​	20-Apr-2014​	16-Apr-2014​
21​	07-Apr-2014​	13-Apr-2014​	09-Apr-2014​
22​	31-Mar-2014​	06-Apr-2014​
23​	24-Mar-2014​	30-Mar-2014​	26-Mar-2014​
24​	17-Mar-2014​	23-Mar-2014​	19-Mar-2014​
25​	10-Mar-2014​	16-Mar-2014​	12-Mar-2014​
26​	03-Mar-2014​	09-Mar-2014​	05-Mar-2014​
27​	24-Feb-2014​	02-Mar-2014​	23-Feb-2014​
28​	17-Feb-2014​	23-Feb-2014​
29​	10-Feb-2014​	16-Feb-2014​	09-Feb-2014​
30​	03-Feb-2014​	09-Feb-2014​	02-Feb-2014​
31​	27-Jan-2014​	02-Feb-2014​	26-Jan-2014​
32​	20-Jan-2014​	26-Jan-2014​
33​	13-Jan-2014​	19-Jan-2014​	12-Jan-2014​
34​	06-Jan-2014​	12-Jan-2014​	05-Jan-2014​
35​	30-Dec-2013​	05-Jan-2014​	29-Dec-2013​
36​	23-Dec-2013​	29-Dec-2013​
37​	16-Dec-2013​	22-Dec-2013​	15-Dec-2013​
38​	09-Dec-2013​	15-Dec-2013​
39​	02-Dec-2013​	08-Dec-2013​
40​	25-Nov-2013​	01-Dec-2013​
41​	18-Nov-2013​	24-Nov-2013​	17-Nov-2013​
42​	11-Nov-2013​	17-Nov-2013​	10-Nov-2013​
43​	04-Nov-2013​	10-Nov-2013​
44​	28-Oct-2013​	03-Nov-2013​	27-Oct-2013​
45​	21-Oct-2013​	27-Oct-2013​	20-Oct-2013​
46​	14-Oct-2013​	20-Oct-2013​	13-Oct-2013​
47​	07-Oct-2013​	13-Oct-2013​	06-Oct-2013​
48​	30-Sep-2013​	06-Oct-2013​	29-Sep-2013​
49​	23-Sep-2013​	29-Sep-2013​
50​	16-Sep-2013​	22-Sep-2013​
51​	09-Sep-2013​	15-Sep-2013​
52​	02-Sep-2013​	08-Sep-2013​
53​	26-Aug-2013​	01-Sep-2013​

I ran AlignKeys, which interleaved the dates in columns B and D, which is what I thought you wanted:

Row\Col	B​	C​	D​
1​	IRI Start​	IRI End​	Kroger ATL​
2​	26-Aug-2013​	01-Sep-2013​
3​	02-Sep-2013​	08-Sep-2013​
4​	09-Sep-2013​	15-Sep-2013​
5​	16-Sep-2013​	22-Sep-2013​
6​	23-Sep-2013​	29-Sep-2013​
7​			29-Sep-2013​
8​	30-Sep-2013​	06-Oct-2013​
9​			06-Oct-2013​
10​	07-Oct-2013​	13-Oct-2013​
11​			13-Oct-2013​
12​	14-Oct-2013​	20-Oct-2013​
13​			20-Oct-2013​
14​	21-Oct-2013​	27-Oct-2013​
15​			27-Oct-2013​
16​	28-Oct-2013​	03-Nov-2013​
17​	04-Nov-2013​	10-Nov-2013​
18​			10-Nov-2013​
19​	11-Nov-2013​	17-Nov-2013​
20​			17-Nov-2013​
21​	18-Nov-2013​	24-Nov-2013​
22​	25-Nov-2013​	01-Dec-2013​
23​	02-Dec-2013​	08-Dec-2013​
24​	09-Dec-2013​	15-Dec-2013​
25​			15-Dec-2013​
26​	16-Dec-2013​	22-Dec-2013​
27​	23-Dec-2013​	29-Dec-2013​
28​			29-Dec-2013​
29​	30-Dec-2013​	05-Jan-2014​
30​			05-Jan-2014​
31​	06-Jan-2014​	12-Jan-2014​
32​			12-Jan-2014​
33​	13-Jan-2014​	19-Jan-2014​
34​	20-Jan-2014​	26-Jan-2014​
35​			26-Jan-2014​
36​	27-Jan-2014​	02-Feb-2014​
37​			02-Feb-2014​
38​	03-Feb-2014​	09-Feb-2014​
39​			09-Feb-2014​
40​	10-Feb-2014​	16-Feb-2014​
41​	17-Feb-2014​	23-Feb-2014​
42​			23-Feb-2014​
43​	24-Feb-2014​	02-Mar-2014​
44​	03-Mar-2014​	09-Mar-2014​
45​			05-Mar-2014​
46​	10-Mar-2014​	16-Mar-2014​
47​			12-Mar-2014​
48​	17-Mar-2014​	23-Mar-2014​
49​			19-Mar-2014​
50​	24-Mar-2014​	30-Mar-2014​
51​			26-Mar-2014​
52​	31-Mar-2014​	06-Apr-2014​
53​	07-Apr-2014​	13-Apr-2014​
54​			09-Apr-2014​
55​	14-Apr-2014​	20-Apr-2014​
56​			16-Apr-2014​
57​	21-Apr-2014​	27-Apr-2014​
58​			23-Apr-2014​
59​	28-Apr-2014​	04-May-2014​
60​			30-Apr-2014​
61​	05-May-2014​	11-May-2014​
62​			07-May-2014​
63​	12-May-2014​	18-May-2014​
64​			14-May-2014​
65​	19-May-2014​	25-May-2014​
66​			21-May-2014​
67​	26-May-2014​	01-Jun-2014​
68​			28-May-2014​
69​	02-Jun-2014​	08-Jun-2014​
70​			04-Jun-2014​
71​	09-Jun-2014​	15-Jun-2014​
72​			11-Jun-2014​
73​	16-Jun-2014​	22-Jun-2014​
74​			18-Jun-2014​
75​	23-Jun-2014​	29-Jun-2014​
76​			25-Jun-2014​
77​	30-Jun-2014​	06-Jul-2014​
78​			02-Jul-2014​
79​	07-Jul-2014​	13-Jul-2014​
80​			09-Jul-2014​
81​	14-Jul-2014​	20-Jul-2014​
82​			16-Jul-2014​
83​	21-Jul-2014​	27-Jul-2014​
84​			23-Jul-2014​
85​	28-Jul-2014​	03-Aug-2014​
86​			30-Jul-2014​
87​	04-Aug-2014​	10-Aug-2014​
88​			06-Aug-2014​
89​	11-Aug-2014​	17-Aug-2014​
90​			13-Aug-2014​
91​	18-Aug-2014​	24-Aug-2014​
92​			20-Aug-2014​