Complex find and replace actions

Hello, I am new to the forum and to VBA, so I apologize in advance if I post this in the wrong place or ask for advice the wrong way. I have limited experience with Excel programming. I’m afraid I have reached the limit of my knowledge and so I decided to seek help. Any guidance or advice on how to code this would be greatly appreciated.

When we receive emails with attachments, each email and attachment is processed and assigned an ID number (Original ID). Each email also has an "Attachment ID" field that references the Original ID for each of its attachments. Each attachment has a "Parent ID" containing the Original ID for the email it was attached to.

For example, an email could be Original ID “ABC1” and have attachments “ABC2” and “ABC3.” In that case, the email’s Attachment ID would show ABC2 and ABC3. The two attachments' Parent ID field would contain ABC1.

When these emails and attachments are produced to an opposing party, not all of the documents are produced. For example, attachment ABC2 might be confidential and not produced. Because of this, the produced documents are renumbered to be sequential, leaving out document ABC2. So the final result might be DEF1 (the email), and DEF2 (originally ABC3).

Here is the crux of the issue. The Attachment ID and the Parent ID fields have to be updated manually to show the new production numbering. This takes days to do manually (there are hundreds or thousands of documents produced at a time). It seems like it should be a fairly short code to update the numbering, but I have been unable to figure out how to do it.

I believe the steps would be:

1) Use a substitute function to convert the Attachment ID and Parent ID fields to remove carriage returns and place a “;” between the items;
2) Loop through the Attachment ID column and remove any numbers that do not exist in the New ID column. (get rid of references to attachments that were not produced)
3) Loop through the Parent ID column and remove any numbers that do not exist in the New ID column. (get rid of references to emails that were not produced)
4) Loop through the Attachment ID column, match each ID to the Original ID column and then replace the Attachment ID entry with the corresponding New ID. (essentially a loop to find and replace on the Original ID with the New ID numbers)
5) Loop through the Parent ID column, match each ID to the Original ID column and replace the Parent ID entry with the corresponding New ID. (essentially a loop to find and replace on the Original ID with the New ID numbers)

I am attaching a spreadsheet with one tab showing the original state of the data and a second tab showing the desired result.

I wish I could provide some code as a starter for this, but it would just be bits and pieces, not really useful. I hope my description makes sense, and that this is not extremely difficult to do.