Macro to combine data from two datasets based on matching strings

Hello everyone,

In summary:
I'd really like your help creating VBA code that looks through two datasets, finds matching strings that start "Title:", and then copies associated rows from one dataset to the other.

This workbook shows the 'before' and desired 'after':
sorting-music-data.xlsx

In detail:
I deal with spreadsheets listing information about music tracks. They're not arranged in a very conventional way - but that's out of my control!

Different pieces of information about the same song appear in two separate sections on the same worksheet.

This info needs to be brought together. This takes a long time to do manually, so I am looking at a VBA solution.

The two sections are called 'Alphabetical Order' and 'In The Order Played'.

Ultimately I need all the tracks to be listed in the same order as in the 'In The Order Played' section.

Each track has at the very least a Title: field; this is present in both sections. This could be used to help match the data in the two sections up.

I attach a sample worksheet showing the 'before' and 'desired' states.

sorting-music-data.xlsx

Notes about the two sections:

• In the 'Alphabetical Order' section, the only mandatory field is Title. The number of additional fields, and their names, varies.
• In the 'In The Order Played' section, three fields are always given about each track: Title, Duration and Offset.

Other Notes:

• In reality there'd be perhaps 10, maybe even 50 separate tracks rather than five.
• The solution code would need to deal with whatever track names were thrown at it.
• Sometimes the same song may appear multiple times in the 'In The Order Played' section, so the same data will need to be fetched from the 'Alphabetical Order' section. It's not a good idea, therefore, to cut or delete anything from this 'alphabetical' section until the end.
• I've used colour on the worksheet to help illustrate the 'before' and 'after' states - but there wouldn't be colour 'in real life'.
• The output needs to be formatted as indicated, so that a computer program at work can parse it. It doesn't mind what order the fields are in, as long as there is a blank line between each new track.

Pseudo-code:
Here's some pseudo-code setting out one way I thought the task might be achieved, but lack the programming knowledge to implement...

For the range between the string "In The Order Played" and the last used cell on the worksheet

For each cell starting with "Title: "

Look at the full text string in this cell and find a matching string in the "Alphabetical Order" section.

When found, in the "Alphabetical Order" section, copy all the rows below the relevant "Title" field, until (but not including) the next blank row

Look at how many rows have just been copied and insert this many rows beneath the appropriate "Title" in the "In The Order Played" section

Paste the rows

Repeat the process by looking at the next cell starting with "Title: " in the "In The Order Played" section

Keep going until the end of the used worksheet is reached

After this loop is over: Delete all of the "Alphabetical Order" section, and the "Alphabetical Order" and "In The Order Played" section titles.

***

I'd really appreciate your help - I'm off to bed now, so apologise if I don't get back to you with a reply right away. I'll be back online in a few hours!

:-) Haha, I can be a bit reluctant to ask for help!

Thanks so much millz - it works a dream. It will make my life a lot easier.

There's one eventuality I hinted at in my opening post, but neglected to include in my sample data. This is where in the 'Alphabetical' section, a track has only a Title, and no other attributes.

Currently when this happens, the code throws up a '1004' run-time error. The debugger highlights the following line:

Please Login or Register  to view this content.

You can see an example in this workbook:
what-if-its-just-the-title.xlsm
It's the absence of a value in the line beneath Title: 4th of July (Instrumental) that seems to be doing it.

But once again, my grateful thanks for your help.

:-) Not lazy at all! Every track ought to have a performer... it's just that sometimes they're missing from my data.

Anyway, your code is absolutely spot on. Thanks very much indeed for your help.