Duplicate row based on cell with text separated by semicolon

[esmarques]

Hello,
Could anybody help with finding a solution / formulas to help me find a way of duplicating a row as many times as there are unique text values, separated by semicolons, in a cell in that row?
The amount of rows will varies as it will be downloaded from a database as often as needed so ideally I would need the formulas in a second worksheet so I can then just copy and paste the data into Data sheet.
In the worksheet attached, this text is in column F. And if there is no data in Column F, just leave it as it is.
So for example, in F2 there are 5 names separated by semicolon so I would like for this to expand to 5 rows with each of these names in Column F and all the other data from that row copied as the example also in the worksheet.

I have added the desired result in "Data expanded" worksheet.

I use Office 365 in a Mac.
Any help welcomed.

Please let me know if you need any further information.

Thank you very much.

Data Worksheet:

Volunteers hs and support.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Resource Booking Start	Resource Booking End	Quantity	Supplier	Event Reference	Staff	Event Date	Activity Detail	Arrangement Reference	Description	Date [Required]	Event Status	Resource	Resource Type	Arrangement Type
2	4/6/24 8:30	4/6/24 17:15	10	Supplier1	2524	Caudwell, Anne-marie; Edmonds, Jill; Nevarez-Pyrkova, Anastasia; Palmer, Andrea; Roberts, Melanie	4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
3	4/6/24 8:30	4/6/24 17:15	10	Supplier2	2868		4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
4	5/25/24 18:30	5/25/24 23:45	3	Supplier3	2593	Palmer, Andrea; Webb, Jude	5/25/24	Performance3	382	Live Music ticketed	5/25/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
5	6/22/24 18:30	6/22/24 23:30	3	Supplier4	2662	Gorman, Janet; Webb, Jude	6/22/24	Performance4	348	Live Ticketed event	6/22/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
6	4/27/24 18:30	4/27/24 22:45	1	Supplier5	2384	Patel, Lina	4/27/24	Performance4	348	Live Ticketed event	4/27/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
7	6/29/24 10:00	6/29/24 14:45	2	Supplier6	3569		6/29/24	Performance6	553	Mill Event	6/29/24	Confirmed	Volunteer	Staff	Networking/relationship building
8	9/21/24 14:30	9/21/24 23:15	4	Supplier7	2900	Gorman, Janet; Knaggs, Mick; Knaggs, Sarah; Palmer, Andrea	9/21/24	Performance6	458	The Vale Third Birthday	9/21/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
Data

Data_Expanded worksheet with desired result:

Volunteers hs and support.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	EXAMPLE RESULT
2	4/6/24 8:30	4/6/24 17:15	10	Supplier1	2524	Caudwell, Anne-marie	4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
3	4/6/24 8:30	4/6/24 17:15	10	Supplier1	2524	Edmonds, Jill	4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
4	4/6/24 8:30	4/6/24 17:15	10	Supplier1	2524	Nevarez-Pyrkova, Anastasia	4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
5	4/6/24 8:30	4/6/24 17:15	10	Supplier1	2524	Palmer, Andrea	4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
6	4/6/24 8:30	4/6/24 17:15	10	Supplier1	2524	Roberts, Melanie	4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
7	5/25/24 18:30	5/25/24 23:45	3	Supplier3	2593	Palmer, Andrea	5/25/24	Performance3	382	Live Music ticketed	5/25/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
8	5/25/24 18:30	5/25/24 23:45	3	Supplier3	2593	Webb, Jude	5/25/24	Performance3	382	Live Music ticketed	5/25/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
9	6/22/24 18:30	6/22/24 23:30	3	Supplier4	2662	Gorman, Janet	6/22/24	Performance4	348	Live Ticketed event	6/22/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
10	6/22/24 18:30	6/22/24 23:30	3	Supplier4	2662	Webb, Jude	6/22/24	Performance4	348	Live Ticketed event	6/22/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
11	4/27/24 18:30	4/27/24 22:45	1	Supplier5	2384	Patel, Lina	4/27/24	Performance4	348	Live Ticketed event	4/27/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
12	9/21/24 14:30	9/21/24 23:15	4	Supplier7	2900	Gorman, Janet	9/21/24	Performance6	458	The Vale Third Birthday	9/21/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
13	9/21/24 14:30	9/21/24 23:15	4	Supplier7	2900	Knaggs, Mick	9/21/24	Performance6	458	The Vale Third Birthday	9/21/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
14	9/21/24 14:30	9/21/24 23:15	4	Supplier7	2900	Knaggs, Sarah	9/21/24	Performance6	458	The Vale Third Birthday	9/21/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
15	9/21/24 14:30	9/21/24 23:15	4	Supplier7	2900	Palmer, Andrea	9/21/24	Performance6	458	The Vale Third Birthday	9/21/24	Confirmed	Volunteer	Staff	Live Event - Ticketed
16	4/6/24 8:30	4/6/24 17:15	10	Supplier2	2868		4/6/24	Performance1	374	Spring event	4/6/24	Confirmed	Volunteer	Staff	Internal
17	6/29/24 10:00	6/29/24 14:45	2	Supplier6	3569		6/29/24	Performance6	553	Mill Event	6/29/24	Confirmed	Volunteer	Staff	Networking/relationship building
Data_expanded

 

[JEC]

simply with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    trf = Table.TransformColumns(Source,{{"Staff", each try Text.Split(_,"; ") otherwise null }}),
    expand = Table.ExpandListColumn(trf, "Staff"),
    result = Table.SelectRows(expand, each ([Staff] <> null))
in
    result

 

[esmarques]

Hello, thank you for your reply. I am not familiar with Power Query, and also as other members of the team will be using this, is it possible to achieve the result with formulas only?

Thank you

 

[JEC]

It is not getting easier with a formula though. The formula will give you all the output in a dynamic array

=DROP(REDUCE("",F2:F8,LAMBDA(a,b,VSTACK(a,LET(x,IF(b="","",TEXTSPLIT(b,,"; ")),HSTACK(IF({1},OFFSET(b,,-1,,-5),x),x,IF({1},OFFSET(b,,1,,9),x)))))),1)

 

[esmarques]

It is not getting easier with a formula though. The formula will give you all the output in a dynamic array

=DROP(REDUCE("",F2:F8,LAMBDA(a,b,VSTACK(a,LET(x,IF(b="","",TEXTSPLIT(b,,"; ")),HSTACK(IF({1},OFFSET(b,,-1,,-5),x),x,IF({1},OFFSET(b,,1,,9),x)))))),1)

Amazing. It worked perfectly. Thank you very very much.

 

[JEC]

You're weclome! cheers