Any Template for Artist Wise Dates?

S

signup

New Member
Joined
Feb 15, 2018
Messages
42
Office Version
  1. 2019
Platform
  1. Windows
I'm working in movies as an AD. I need to prepare a schedule and provide artist-wise dates to my team. Every time I'm doing it manually by checking the scenes. Can anyone suggest an idea or workaround which could make this work easy?
If there is any way please let me know.

Present template: I'm writing the names & dates manually.

Artist Name​
August Dates​
September Dates​
William16, 18, 19, 24, 26, 3008, 17, 18, 25
James07, 09, 11, 14, 18, 19, 3106, 16, 17, 18, 25
Benjamin08, 16, 18, 2109, 14, 16, 17, 21, 26
Michael05, 07, 08, 11, 15, 17, 21, 25, 3108, 14, 16, 18, 22, 25, 30
Stephen07, 09, 22, 2604, 09, 11, 19, 28
Jessica06, 09, 13, 15, 17, 21, 26
Esther28, 29, 3105, 09, 22, 21, 30
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Store data in a table, then add data to data model and use concatenatex() to build a pivot:
Book1
ABCDEFGHI
1Artist NameDateMonthDay
2William16/08/2021816DaysMonth
3William18/08/2021818Artist Name89
4William19/08/2021819Benjamin8, 16, 18, 219, 14, 16, 17, 21, 26
5William24/08/2021824Esther28, 29, 315, 9, 21, 22, 30
6William26/08/2021826James7, 9, 11, 14, 18, 19, 316, 16, 17, 18, 25
7William30/08/2021830Jessica6, 9, 13, 15, 17, 21, 26
8William8/09/202198Michael5, 7, 8, 11, 15, 17, 21, 25, 318, 14, 16, 18, 22, 25, 30
9William17/09/2021917Stephen7, 9, 22, 264, 9, 11, 19, 28
10William18/09/2021918William16, 18, 19, 24, 26, 308, 17, 18, 25
11William25/09/2021925
12James7/08/202187
13James9/08/202189
14James11/08/2021811
15James14/08/2021814
16James18/08/2021818
17James19/08/2021819
18James31/08/2021831
19James6/09/202196
20James16/09/2021916
21James17/09/2021917
22James18/09/2021918
23James25/09/2021925
24Benjamin8/08/202188
25Benjamin16/08/2021816
26Benjamin18/08/2021818
27Benjamin21/08/2021821
28Benjamin9/09/202199
29Benjamin14/09/2021914
30Benjamin16/09/2021916
31Benjamin17/09/2021917
32Benjamin21/09/2021921
33Benjamin26/09/2021926
34Michael5/08/202185
35Michael7/08/202187
36Michael8/08/202188
37Michael11/08/2021811
38Michael15/08/2021815
39Michael17/08/2021817
40Michael21/08/2021821
41Michael25/08/2021825
42Michael31/08/2021831
43Michael8/09/202198
44Michael14/09/2021914
45Michael16/09/2021916
46Michael18/09/2021918
47Michael22/09/2021922
48Michael25/09/2021925
49Michael30/09/2021930
50Stephen7/08/202187
51Stephen9/08/202189
52Stephen22/08/2021822
53Stephen26/08/2021826
54Stephen4/09/202194
55Stephen9/09/202199
56Stephen11/09/2021911
57Stephen19/09/2021919
58Stephen28/09/2021928
59Jessica6/09/202196
60Jessica9/09/202199
61Jessica13/09/2021913
62Jessica15/09/2021915
63Jessica17/09/2021917
64Jessica21/09/2021921
65Jessica26/09/2021926
66Esther28/08/2021828
67Esther29/08/2021829
68Esther31/08/2021831
69Esther5/09/202195
70Esther9/09/202199
71Esther22/09/2021922
72Esther21/09/2021921
73Esther30/09/2021930
Sheet2
Cell Formulas
RangeFormula
C2:C73C2=MONTH([@Date])
D2:D73D2=DAY([@Date])


DAX measure in Power Pivot
Excel Formula: 
=CONCATENATEX(Table1;Table1[Day];", ";Table1[Date])
 
Upvote 0
With formulas, did not find an all dynamic solution though. And maybe the year needs to be added, depending on how data evolves or not.
Book1
ABCDEFG
1Artist NameDate89
2William16/08/2021William16, 18, 19, 24, 26, 3008, 17, 18, 25
3William18/08/2021James07, 09, 11, 14, 18, 19, 3106, 16, 17, 18, 25
4William19/08/2021Benjamin08, 16, 18, 2109, 14, 16, 17, 21, 26
5William24/08/2021Michael05, 07, 08, 11, 15, 17, 21, 25, 3108, 14, 16, 18, 22, 25, 30
6William26/08/2021Stephen07, 09, 22, 2604, 09, 11, 19, 28
7William30/08/2021Jessica 06, 09, 13, 15, 17, 21, 26
8William8/09/2021Esther28, 29, 3105, 09, 21, 22, 30
9William17/09/2021
10William18/09/2021
11William25/09/2021
12James7/08/2021
13James9/08/2021
14James11/08/2021
15James14/08/2021
16James18/08/2021
17James19/08/2021
18James31/08/2021
19James6/09/2021
20James16/09/2021
21James17/09/2021
22James18/09/2021
23James25/09/2021
24Benjamin8/08/2021
25Benjamin16/08/2021
26Benjamin18/08/2021
27Benjamin21/08/2021
28Benjamin9/09/2021
29Benjamin14/09/2021
30Benjamin16/09/2021
31Benjamin17/09/2021
32Benjamin21/09/2021
33Benjamin26/09/2021
34Michael5/08/2021
35Michael7/08/2021
36Michael8/08/2021
37Michael11/08/2021
38Michael15/08/2021
39Michael17/08/2021
40Michael21/08/2021
41Michael25/08/2021
42Michael31/08/2021
43Michael8/09/2021
44Michael14/09/2021
45Michael16/09/2021
46Michael18/09/2021
47Michael22/09/2021
48Michael25/09/2021
49Michael30/09/2021
50Stephen7/08/2021
51Stephen9/08/2021
52Stephen22/08/2021
53Stephen26/08/2021
54Stephen4/09/2021
55Stephen9/09/2021
56Stephen11/09/2021
57Stephen19/09/2021
58Stephen28/09/2021
59Jessica6/09/2021
60Jessica9/09/2021
61Jessica13/09/2021
62Jessica15/09/2021
63Jessica17/09/2021
64Jessica21/09/2021
65Jessica26/09/2021
66Esther28/08/2021
67Esther29/08/2021
68Esther31/08/2021
69Esther5/09/2021
70Esther9/09/2021
71Esther22/09/2021
72Esther21/09/2021
73Esther30/09/2021
Sheet1
Cell Formulas
RangeFormula
F1:G1F1=TRANSPOSE(UNIQUE(MONTH(Table1[Date])))
E2:E8E2=UNIQUE(Table1[Artist Name])
F2:G8F2=IFERROR(TEXTJOIN(", ",TRUE,SORT(FILTER(TEXT(DAY(Table1[[Date]:[Date]]),"00"),(MONTH(Table1[[Date]:[Date]])=F$1)*(Table1[[Artist Name]:[Artist Name]]=$E2)),1,1)),"")
Dynamic array formulas.
 
Upvote 0
GraH said:
Store data in a table, then add data to data model and use concatenatex() to build a pivot:
Book1
ABCDEFGHI
1Artist NameDateMonthDay
2William16/08/2021816DaysMonth
3William18/08/2021818Artist Name89
4William19/08/2021819Benjamin8, 16, 18, 219, 14, 16, 17, 21, 26
5William24/08/2021824Esther28, 29, 315, 9, 21, 22, 30
6William26/08/2021826James7, 9, 11, 14, 18, 19, 316, 16, 17, 18, 25
7William30/08/2021830Jessica6, 9, 13, 15, 17, 21, 26
8William8/09/202198Michael5, 7, 8, 11, 15, 17, 21, 25, 318, 14, 16, 18, 22, 25, 30
9William17/09/2021917Stephen7, 9, 22, 264, 9, 11, 19, 28
10William18/09/2021918William16, 18, 19, 24, 26, 308, 17, 18, 25
11William25/09/2021925
12James7/08/202187
13James9/08/202189
14James11/08/2021811
15James14/08/2021814
16James18/08/2021818
17James19/08/2021819
18James31/08/2021831
19James6/09/202196
20James16/09/2021916
21James17/09/2021917
22James18/09/2021918
23James25/09/2021925
24Benjamin8/08/202188
25Benjamin16/08/2021816
26Benjamin18/08/2021818
27Benjamin21/08/2021821
28Benjamin9/09/202199
29Benjamin14/09/2021914
30Benjamin16/09/2021916
31Benjamin17/09/2021917
32Benjamin21/09/2021921
33Benjamin26/09/2021926
34Michael5/08/202185
35Michael7/08/202187
36Michael8/08/202188
37Michael11/08/2021811
38Michael15/08/2021815
39Michael17/08/2021817
40Michael21/08/2021821
41Michael25/08/2021825
42Michael31/08/2021831
43Michael8/09/202198
44Michael14/09/2021914
45Michael16/09/2021916
46Michael18/09/2021918
47Michael22/09/2021922
48Michael25/09/2021925
49Michael30/09/2021930
50Stephen7/08/202187
51Stephen9/08/202189
52Stephen22/08/2021822
53Stephen26/08/2021826
54Stephen4/09/202194
55Stephen9/09/202199
56Stephen11/09/2021911
57Stephen19/09/2021919
58Stephen28/09/2021928
59Jessica6/09/202196
60Jessica9/09/202199
61Jessica13/09/2021913
62Jessica15/09/2021915
63Jessica17/09/2021917
64Jessica21/09/2021921
65Jessica26/09/2021926
66Esther28/08/2021828
67Esther29/08/2021829
68Esther31/08/2021831
69Esther5/09/202195
70Esther9/09/202199
71Esther22/09/2021922
72Esther21/09/2021921
73Esther30/09/2021930
Sheet2
Cell Formulas
RangeFormula
C2:C73C2=MONTH([@Date])
D2:D73D2=DAY([@Date])


DAX measure in Power Pivot
Excel Formula: 
=CONCATENATEX(Table1;Table1[Day];", ";Table1[Date])
Click to expand...
Thank you so much @GraH for your response. However, This could be an extension of my actual work. Maybe going manual would be the best solution for this?
Else Plz let me know can we achieve this by below method?

I can store Day-wise artists in each worksheet. So, I would have 30 sheets(For Ex.) in Excel Workbook. Can we get automated dates in the last sheet(31st) as shown in the above post?
 
Upvote 0
Yes, that's possible. In that scenario I would go for a Power Query solution/automation.
 
Upvote 0
GraH said:
Yes, that's possible. In that scenario I would go for a Power Query solution/automation.
Click to expand...
Ok, If possible can you please give me a code? I have no knowledge of Power Query.
Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
138
SFCChase
SFCChase
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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