Day calendar referencing array with duplicates

joercav

New Member
Joined
Feb 14, 2022
Messages
8
Platform
  1. MacOS
Any help would be appreciated. I have a dynamic table of dates. Each column references entire production cycles and each row the cycle stages, with the top row being the start and the bottom the end. I am trying to create a "day calendar" on new sheet with the first column listing every day from today and 10 years into the future. To the right of each day, I'd like to show which production cycle and stage is active. On any given day there will be a handful active at the same time. A solution would be helpful because each cycle-stage has inputs that can be listed as well and sums made.

Cav Farm Management.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1141516171819202122232425262728293031323334
214/10/218/29/2112/8/214/17/227/24/2210/23/2212/2/2212/30/223/10/234/24/236/8/238/2/238/30/2310/25/2312/9/231/22/242/21/243/22/244/21/245/21/246/20/24
324/12/218/31/2112/10/214/19/227/26/2210/25/2212/4/221/1/233/12/234/26/236/10/238/4/239/1/2310/27/2312/11/231/24/242/23/243/24/244/23/245/23/246/22/24
434/24/219/12/2112/22/215/1/228/7/2211/6/2212/16/221/13/233/24/235/8/236/22/238/16/239/13/2311/8/2312/23/232/5/243/6/244/5/245/5/246/4/247/4/24
545/1/219/19/2112/29/215/8/228/14/2211/13/2212/23/221/20/233/31/235/15/236/29/238/23/239/20/2311/15/2312/30/232/12/243/13/244/12/245/12/246/11/247/11/24
655/4/219/19/2112/29/215/8/228/14/2211/13/2212/23/221/20/233/31/235/15/236/29/238/23/239/20/2311/15/2312/30/232/12/243/13/244/12/245/12/246/11/247/11/24
766/28/2111/7/212/22/227/2/2210/8/221/7/232/16/233/16/235/25/237/9/238/23/2310/17/2311/14/231/9/242/23/244/7/245/7/246/6/247/6/248/5/249/4/24
878/18/211/6/224/17/228/25/2212/1/223/2/234/11/235/9/237/18/239/1/2310/16/2312/10/231/7/243/3/244/17/245/31/246/30/247/30/248/29/249/28/2410/28/24
988/26/211/13/224/24/229/1/2212/8/223/9/234/18/235/16/237/25/239/8/2310/23/2312/17/231/14/243/10/244/24/246/7/247/7/248/6/249/5/2410/5/2411/4/24
1099/16/212/3/225/15/229/22/2212/29/223/30/235/9/236/6/238/15/239/29/2311/13/231/7/242/4/243/31/245/15/246/28/247/28/248/27/249/26/2410/26/2411/25/24
11109/23/212/14/225/26/2210/3/221/9/234/10/235/20/236/17/238/26/2310/10/2311/24/231/18/242/15/244/11/245/26/247/9/248/8/249/7/2410/7/2411/6/2412/6/24
121111/11/213/31/227/10/2211/17/222/23/235/25/237/4/238/1/2310/10/2311/24/231/8/243/3/243/31/245/26/247/10/248/23/249/22/2410/22/2411/21/2412/21/241/20/25
131211/18/214/7/227/17/2211/24/223/2/236/1/237/11/238/8/2310/17/2312/1/231/15/243/10/244/7/246/2/247/17/248/30/249/29/2410/29/2411/28/2412/28/241/27/25
141311/25/214/14/227/24/2212/1/223/9/236/8/237/18/238/15/2310/24/2312/8/231/22/243/17/244/14/246/9/247/24/249/6/2410/6/2411/5/2412/5/241/4/252/3/25
15144/10/218/29/2112/8/214/17/227/24/2210/23/2212/2/2212/30/223/10/234/24/236/8/238/2/238/30/2310/25/2312/9/231/22/242/21/243/22/244/21/245/21/246/20/24
16155/1/219/19/2112/29/215/8/228/14/2211/13/2212/23/221/20/233/31/235/15/236/29/238/23/239/20/2311/15/2312/30/232/12/243/13/244/12/245/12/246/11/247/11/24
171612/9/214/28/228/7/2212/15/223/23/236/22/238/1/238/29/2311/7/2312/22/232/5/243/31/244/28/246/23/248/7/249/20/2410/20/2411/19/2412/19/241/18/252/17/25
181710/7/212/28/226/9/2210/17/221/23/234/24/236/3/237/1/239/9/2310/24/2312/8/232/1/242/29/244/25/246/9/247/23/248/22/249/21/2410/21/2411/20/2412/20/24
191812/16/215/5/228/14/2212/22/223/30/236/29/238/8/239/5/2311/14/2312/29/232/12/244/7/245/5/246/30/248/14/249/27/2410/27/2411/26/2412/26/241/25/252/24/25
20191/13/226/2/229/11/221/19/234/27/237/27/239/5/2310/3/2312/12/231/26/243/11/245/5/246/2/247/28/249/11/2410/25/2411/24/2412/24/241/23/252/22/253/24/25
21202/10/226/30/2210/9/222/16/235/25/238/24/2310/3/2310/31/231/9/242/23/244/8/246/2/246/30/248/25/2410/9/2411/22/2412/22/241/21/252/20/253/22/254/21/25
22213/10/227/28/2211/6/223/16/236/22/239/21/2310/31/2311/28/232/6/243/22/245/6/246/30/247/28/249/22/2411/6/2412/20/241/19/252/18/253/20/254/19/255/19/25
23224/7/228/25/2212/4/224/13/237/20/2310/19/2311/28/2312/26/233/5/244/19/246/3/247/28/248/25/2410/20/2412/4/241/17/252/16/253/18/254/17/255/17/256/16/25
24235/5/229/22/221/1/235/11/238/17/2311/16/2312/26/231/23/244/2/245/17/247/1/248/25/249/22/2411/17/241/1/252/14/253/16/254/15/255/15/256/14/257/14/25
25246/2/2210/20/221/29/236/8/239/14/2312/14/231/23/242/20/244/30/246/14/247/29/249/22/2410/20/2412/15/241/29/253/14/254/13/255/13/256/12/257/12/258/11/25
Sheet1
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
What version of Excel do you have? If you have Excel 365, or Excel 2021, then try:

Cell Formulas
RangeFormula
A2:A362A2=UNIQUE(SMALL(Sheet2!B2:V25,SEQUENCE(COUNT(Sheet2!B2:V25))))
B2:B15B2=TEXTJOIN(": ",1,IF(Sheet2!$B$2:$V$25=A2,"Cycle "&Sheet2!$B$1:$V$1&", Stage "&Sheet2!$A$2:$A$25,""))
Dynamic array formulas.


If you have Excel 2019, I can adapt this. Anything older than that will probably require VBA.
 
Upvote 0
Thanks for the help! I have 365 ver 16.58 running on mac. Could you help some more? I'd like to have each and every day listed:

Cav Farm Management.xlsx
AB
51/4/2213-5
61/5/2213-5
71/6/2214-4; 13-5
81/7/2214-4; 13-5
91/8/2214-4; 13-5
101/9/2214-4; 13-5
111/10/2214-4; 13-5
Calendar
 
Upvote 0
You mean every day, including the days that don't have a stage on it? Try:

Book1
AB
1DateStages
24/10/202114-1; 14-14
34/11/2021 
44/12/202114-2
54/13/2021 
64/14/2021 
74/15/2021 
84/16/2021 
94/17/2021 
104/18/2021 
114/19/2021 
124/20/2021 
134/21/2021 
144/22/2021 
154/23/2021 
164/24/202114-3
174/25/2021 
184/26/2021 
194/27/2021 
204/28/2021 
214/29/2021 
224/30/2021 
235/1/202114-4; 14-15
245/2/2021 
255/3/2021 
265/4/202114-5
275/5/2021 
285/6/2021 
295/7/2021 
305/8/2021 
Sheet3
Cell Formulas
RangeFormula
A2:A1586A2=SEQUENCE(MAX(Sheet2!B2:V25)-MIN(Sheet2!B2:V25)+1,,MIN(Sheet2!B2:V25))
B2:B30B2=TEXTJOIN("; ",1,IF(Sheet2!$B$2:$V$25=A2,Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$25,""))
Dynamic array formulas.
 
Upvote 0
Yes. But the days that are showing empty cells actually have ongoing cycle stages. The first row of each column is the start of the cycle with the initial stage #1, that stage runs until #2 starts. The bottom row stage #24 marks the end of the entire cycle and has no subsequent days.
Thanks for your help on this!
 
Upvote 0
OK, try:

Book1
AB
1DateStages
24/10/202114-1; 14-14
34/11/202114-1; 14-14
44/12/202114-2; 14-14
54/13/202114-2; 14-14
64/14/202114-2; 14-14
74/15/202114-2; 14-14
84/16/202114-2; 14-14
94/17/202114-2; 14-14
104/18/202114-2; 14-14
Sheet3
Cell Formulas
RangeFormula
A2:A1586A2=SEQUENCE(MAX(Sheet2!B2:V25)-MIN(Sheet2!B2:V25)+1,,MIN(Sheet2!B2:V25))
B2:B10B2=TEXTJOIN("; ",1,IF((A2>=Sheet2!$B$2:$V$25)*(A2<Sheet2!$B$3:$V$26),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$25,""))
Dynamic array formulas.


Some of the later rows are quite large though:

Cell Formulas
RangeFormula
B1581:B1586B1581=TEXTJOIN("; ",1,IF((A1581>=Sheet2!$B$2:$V$25)*(A1581<Sheet2!$B$3:$V$26),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$25,""))
 
Upvote 0
Thanks for your help. But that doesnt look right to me. Using this sheet of dates I first posted, on 8/6/25, there should only be 34-24, because all other stages ended on their "end" day (25) and should not show up ever again. On 8/11/25, Cycle 34 hits its "end" day (25) and should be 34-25, and there after cycle 34 won't show again.
If you can find a solution, I'd be super appreciative- Thank you!
 
Upvote 0
Whoops! Sorry, I missed your comment where you said there are no days past stage 24, and I just used a very large date. Try this:

Book1
AB
1DateStages
24/10/202114-1; 14-14
34/11/202114-1; 14-14
44/12/202114-2; 14-14
54/13/202114-2; 14-14
64/14/202114-2; 14-14
74/15/202114-2; 14-14
84/16/202114-2; 14-14
94/17/202114-2; 14-14
104/18/202114-2; 14-14
114/19/202114-2; 14-14
124/20/202114-2; 14-14
134/21/202114-2; 14-14
144/22/202114-2; 14-14
154/23/202114-2; 14-14
Sheet3
Cell Formulas
RangeFormula
A2:A1558A2=SEQUENCE(MAX(Sheet2!B2:V24)-MIN(Sheet2!B2:V24)+1,,MIN(Sheet2!B2:V24))
B2:B15B2=TEXTJOIN("; ",1,IF((A2>=Sheet2!$B$2:$V$24)*(A2<Sheet2!$B$3:$V$25),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$24,""))
Dynamic array formulas.


and at the end:

Book1
AB
15446/30/202534-22; 33-23
15457/1/202534-22; 33-23
15467/2/202534-22; 33-23
15477/3/202534-22; 33-23
15487/4/202534-22; 33-23
15497/5/202534-22; 33-23
15507/6/202534-22; 33-23
15517/7/202534-22; 33-23
15527/8/202534-22; 33-23
15537/9/202534-22; 33-23
15547/10/202534-22; 33-23
15557/11/202534-22; 33-23
15567/12/202534-22
15577/13/202534-22
15587/14/202534-23
1559 
Sheet3
Cell Formulas
RangeFormula
B1544:B1559B1544=TEXTJOIN("; ",1,IF((A1544>=Sheet2!$B$2:$V$24)*(A1544<Sheet2!$B$3:$V$25),Sheet2!$B$1:$V$1&"-"&Sheet2!$A$2:$A$24,""))
 
Upvote 0
Again, thanks for your help, but I'm not able to duplicate your same results. Not sure why, I'm getting blanks...
 
Upvote 0
Many thanks. I just learned a whole bunch from you. To make it work I modified the tables and your formula. I added "end dates" below the original start dates and used this formula =TEXTJOIN(";",TRUE,IF($A787>='Cycle Schedule'!$S$66:$CE$93,IF($A787<'Cycle Schedule'!$S$96:$CE$123,'Cycle Schedule'!$S$1:$CE$1&"-"&'Cycle Schedule'!$B$96:$B$123,""),""))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top