Row Duplication With Tricky Variables

Hi there,

Having had some extremely helpful responses on this forum in the recent past, I thought I'd return for some more Excel Macro/VBA help.

I've attached an example spreadsheet with the problem.

1. I work with timetable data sets which are often thousands of rows in length.

2. Each row in the example spreadsheet represents a booking, but the data is often a mixture of bookings in single and multiple locations. Some rows are designated for only one room, while others contain multiple locations in the "Location" column.

3. I've been trying to devise an automated method of turning the data as it is in the "Initial Data" tab to how it looks in the "Desired Outcome" tab.

4. What I need is for a Macro that works through the data, identifying when a booking (i.e. a row ) has multiple locations attributed to it. The macro then needs to copy the entire row, and copy/insert as many rows as are necessary underneath in order to produce one row per location. All of the other data remains the same - it is just the location that needs to be unique in each of the new copied/inserted rows.

5. The main challenge here is that the locations are often separated by various characters, such as "/" and ",", but can also be separated by other characters as well. If there is no way to produce a macro that can identify which, then a solution that can be easily edited to look for specific characters (defined by the user) between locations would be desirable.


6. The data is often thousands of rows in length, so the macro needs to continue working until no more data exists to process.


At its core, this is another (relatively) simple duplication macro, but with some variables (i.e. the number of different locations and the characters separating each location) that make this kind of macro way out of my technical depth. It's all about the "Location" column, with the remaining columns needing simply to be copied identically, with only the Location differing in each resulting duplicated row.

The manual method for this (text to columns with specified character as delimiter, then copying/pasting the required number of rows below the data, then copying the text-to-columns locations and transpose-pasting them down in the location column) takes way, way too long when there are thousands of rows to work with!

I appreciate any advice and input on the matter!