Combine Multiple Matches & Separate by Semicolon

[redhots4]

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

 

[Fluff]

How about

=TEXTJOIN("; ",,FILTER($B$2:$B$1000,$E$2:$E$1000=E2))

 

[redhots4]

Wow, thank you Fluff for a quick and working solution!

 

[Fluff]

You're welcome & thanks for the feedback.