Duplicate Rows when one field has multiple results

Hey all,

I have a couple sheets with a simple ask, that is difficult in practice. See the attached image for a visual example of what I need.

Basically, I have a file, about 50k rows and about 50 columns.

Each row contains sales information, including dates, products, dollars, customers, and managers.


Unfortunately, some of the customers have multiple managers, and these multiple managers are listed in the same row with an ampersand (John Doe & Jane Doe)

What I need to do is find rows which have this, which is only about 8% of the rows, and make two copies of the rows. The mapping of the names isn't the hard part, I can just use an index/count type formula to put name A in the first row and name B in the second. The main snag is copying these rows. There can be up to 7 names in a single cell, meaning that particular row of data needs to be copied 7 times.

My first instinct says "dump everything in SQL and force a Cartesian product on the name field", but i'd rather not take that approach if it can be done formulaically
SplitRows.jpg
Thoughts?

Figured out a solution:

Add an index number to each row (1, 2, 3, and so on)
Add a second index number for each "block" (1, 1, 1, 1 for entire set)
Copy all data and paste below, on pasted data, second index number is 2, 2, 2, 2
Repeat for each possible required duplication - 7 in my case
Sort data, two levels, so you get 1, 1, 1, 1, 1, 1, 1 in Index, and 1, 2, 3, 4, 5, 6, 7 in the second index
Text To Columns on the target names, by & to break out all the names into individual columns
Offset/Index/Match against the second index number to pull the Nth name in

Remove both index columns, select all, remove duplicates.