Hi everyone - I need help with a readings list at church. In my spreadsheet, individual readings are listed on separate lines but I want to combine all readings for a given day into a single cell. For each unique value in the "ACT Code" column, I want to concatenate the values in "Scripture Citation", separated by semicolons between each. For example, AAIIS should return "Psalm 34:1-10, 22; Matthew 5:1-12; 1 John 3:1-3; Revelation 7:9-17" in Cell F2 (and other rows where AAIIS appears). Thanks in advance!
Lectionary 2022-2023.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Timing | Scripture Citation | Year | Season | ACT Code | ||
2 | All Saints Day | Psalm 34:1-10, 22 | Year A | Season after Pentecost | AAllS | ||
3 | All Saints Day | Matthew 5:1-12 | Year A | Season after Pentecost | AAllS | ||
4 | Easter Vigil | Genesis 1:1-2:4a | Year B | Easter | BVigil | ||
5 | Easter Vigil | Genesis 1:1-2:4a | Year C | Easter | CVigil | ||
6 | Baptism of the Lord | Genesis 1:1-5 | Year B | Epiphany | BEpip01 | ||
7 | All Saints Day | 1 John 3:1-3 | Year A | Season after Pentecost | AAllS | ||
8 | Proper 22 (27) | Genesis 2:18-24 | Year B | Season after Pentecost | BProp22 | ||
9 | Proper 5 (10) | Genesis 3:8-15 | Year B | Season after Pentecost | BProp05 | ||
10 | All Saints Day | Revelation 7:9-17 | Year A | Season after Pentecost | AAllS | ||
11 | Annunciation of the Lord | Psalm 40:5-10 | Year A | Lent | AAnLo | ||
12 | Easter Vigil | Genesis 7:1-5, 11-18; 8:6-18; 9:8-13 | Year B | Easter | BVigil | ||
13 | Easter Vigil | Genesis 7:1-5, 11-18; 8:6-18; 9:8-13 | Year C | Easter | CVigil | ||
14 | First Sunday in Lent | Genesis 9:8-17 | Year B | Lent | BLent01 | ||
15 | Day of Pentecost | Genesis 11:1-9 | Year C | Easter | CPDay | ||
16 | Annunciation of the Lord | Psalm 45 | Year A | Lent | AAnLo | ||
17 | Annunciation of the Lord | Isaiah 7:10-14 | Year A | Lent | AAnLo | ||
18 | Proper 14 (19) | Genesis 15:1-6 | Year C | Season after Pentecost | CProp14 | ||
19 | Second Sunday in Lent | Genesis 15:1-12, 17-18 | Year C | Lent | CLent02 | ||
20 | Second Sunday in Lent | Genesis 17:1-7, 15-16 | Year B | Lent | BLent02 | ||
21 | Proper 11 (16) | Genesis 18:1-10a | Year C | Season after Pentecost | CProp11 | ||
22 | Annunciation of the Lord | Luke 1:26-38 | Year A | Lent | AAnLo | ||
23 | Proper 12 (17) | Genesis 18:20-32 | Year C | Season after Pentecost | CProp12 | ||
24 | Annunciation of the Lord | Hebrews 10:4-10 | Year A | Lent | AAnLo | ||
25 | Ascension of the Lord | Psalm 47 | Year A | Easter | AAscn | ||
26 | Ascension of the Lord | Psalm 93 | Year A | Easter | AAscn | ||
27 | Easter Vigil | Genesis 22:1-18 | Year B | Easter | BVigil | ||
28 | Easter Vigil | Genesis 22:1-18 | Year C | Easter | CVigil | ||
29 | Ascension of the Lord | Luke 24:44-53 | Year A | Easter | AAscn | ||
30 | Ascension of the Lord | Acts 1:1-11 | Year A | Easter | AAscn | ||
31 | Ascension of the Lord | Ephesians 1:15-23 | Year A | Easter | AAscn | ||
32 | Ash Wednesday | Psalm 51:1-17 | Year A | Lent | AAshW | ||
33 | Ash Wednesday | Isaiah 58:1-12 | Year A | Lent | AAshW | ||
34 | Proper 24 (29) | Genesis 32:22-31 | Year C | Season after Pentecost | CProp24 | ||
35 | Ash Wednesday | Joel 2:1-2, 12-17 | Year A | Lent | AAshW | ||
36 | Ash Wednesday | Matthew 6:1-6, 16-21 | Year A | Lent | AAshW | ||
37 | Seventh Sunday after the Epiphany | Genesis 45:3-11, 15 | Year C | Epiphany | CEpip07 | ||
38 | Ash Wednesday | 2 Corinthians 5:20b-6:10 | Year A | Lent | AAshW | ||
39 | Baptism of the Lord | Psalm 29 | Year A | Epiphany | AEpip01 | ||
40 | Baptism of the Lord | Isaiah 42:1-9 | Year A | Epiphany | AEpip01 | ||
41 | Baptism of the Lord | Matthew 3:13-17 | Year A | Epiphany | AEpip01 | ||
42 | Maundy Thursday | Exodus 12:1-4, (5-10), 11-14 | Year B | Holy Week | BHoly04 | ||
43 | Maundy Thursday | Exodus 12:1-4, (5-10), 11-14 | Year C | Holy Week | CHoly04 | ||
44 | Baptism of the Lord | Acts 10:34-43 | Year A | Epiphany | AEpip01 | ||
45 | Canadian Thanksgiving Day | Deuteronomy 8:7-18 | Year A | Season after Pentecost | ATDay | ||
46 | Easter Vigil | Exodus 14:10-31; 15:20-21 | Year B | Easter | BVigil | ||
47 | Easter Vigil | Exodus 14:10-31; 15:20-21 | Year C | Easter | CVigil | ||
48 | Canadian Thanksgiving Day | Psalm 65 | Year A | Season after Pentecost | ATDay | ||
49 | Canadian Thanksgiving Day | Luke 17:11-19 | Year A | Season after Pentecost | ATDay | ||
50 | Canadian Thanksgiving Day | 2 Corinthians 9:6-15 | Year A | Season after Pentecost | ATDay | ||
51 | Easter Vigil | Exodus 15:1b-13, 17-18 | Year B | Easter | BVigil | ||
52 | Easter Vigil | Exodus 15:1b-13, 17-18 | Year C | Easter | CVigil | ||
53 | Proper 13 (18) | Exodus 16:2-4, 9-15 | Year B | Season after Pentecost | BProp13 | ||
54 | Day of Pentecost | Numbers 11:24-30 | Year A | Easter | APDay | ||
55 | Day of Pentecost | Psalm 104:24-34, 35b | Year A | Easter | APDay | ||
56 | Day of Pentecost | John 7:37-39 | Year A | Easter | APDay | ||
57 | Day of Pentecost | John 20:19-23 | Year A | Easter | APDay | ||
58 | Day of Pentecost | Acts 2:1-21 | Year A | Easter | APDay | ||
59 | Third Sunday in Lent | Exodus 20:1-17 | Year B | Lent | BLent03 | ||
60 | Day of Pentecost | 1 Corinthians 12:3b-13 | Year A | Easter | APDay | ||
61 | Easter Evening | Psalm 114 | Year A | Easter | ARess | ||
62 | Proper 19 (24) | Exodus 32:7-14 | Year C | Season after Pentecost | CProp19 | ||
63 | Easter Evening | Isaiah 25:6-9 | Year A | Easter | ARess | ||
64 | Transfiguration Sunday | Exodus 34:29-35 | Year C | Epiphany | CTran | ||
65 | Easter Evening | Luke 24:13-49 | Year A | Easter | ARess | ||
66 | Easter Evening | 1 Corinthians 5:6b-8 | Year A | Easter | ARess | ||
67 | Easter Vigil | Genesis 1:1-2:4a | Year A | Easter | AVigil | ||
68 | Holy Name of Jesus | Numbers 6:22-27 | Year B | Christmas | BHNam | ||
69 | Holy Name of Jesus | Numbers 6:22-27 | Year C | Christmas | CHNam | ||
70 | Proper 21 (26) | Numbers 11:4-6, 10-16, 24-29 | Year B | Season after Pentecost | BProp21 | ||
71 | Easter Vigil | Genesis 7:1-5, 11-18; 8:6-18; 9:8-13 | Year A | Easter | AVigil | ||
72 | Fourth Sunday in Lent | Numbers 21:4-9 | Year B | Lent | BLent04 | ||
73 | Easter Vigil | Genesis 22:1-18 | Year A | Easter | AVigil | ||
74 | Holy Cross | Numbers 21:4b-9 | Year B | Season after Pentecost | BHoCr | ||
75 | Holy Cross | Numbers 21:4b-9 | Year C | Season after Pentecost | CHoCr | ||
76 | Proper 17 (22) | Deuteronomy 4:1-2, 6-9 | Year B | Season after Pentecost | BProp17 | ||
77 | Ninth Sunday after the Epiphany | Deuteronomy 5:12-15 | Year B | Epiphany | BEpip09 | ||
78 | Proper 4 (9) | Deuteronomy 5:12-15 | Year B | Season after Pentecost | BProp04 | ||
79 | Proper 26 (31) | Deuteronomy 6:1-9 | Year B | Season after Pentecost | BProp26 | ||
80 | Easter Vigil | Exodus 14:10-31; 15:20-21 | Year A | Easter | AVigil | ||
81 | Easter Vigil | Exodus 15:1b-13, 17-18 | Year A | Easter | AVigil | ||
82 | Easter Vigil | Psalm 16 | Year A | Easter | AVigil | ||
83 | Easter Vigil | Psalm 19 | Year A | Easter | AVigil | ||
3 Yr Lectionary |