VBA Text based Help (Pseudo Code Provided) - Hard/difficult/expert level?

Hi Everyone,

So I need help with turning my pseudo code into VBA code.

It's all text-based manipulation, and I've been struggling for 4 days with no success.

-----------------------------------------------------------------------------------------------------------------

Data

I have a column called "Playwrite" which is basically a conversation between two people.

Each cell has an entire conversation between two people. A double-pipeline delimits them. Or I can use Find&Replace to break the || delimiter into Char(10), a newline.

Example of cell $A$2

Susan: Hi how was your day?||Jack: Today was good||Susan: That's great, did you have breakfast?||Susan: Cause I had a lot to eat this morning!||Susan: It was refreshing too!||Jack: Yep I had a good breakfast today.||Susan: This conversation is closed.

Or, I can use find&replace to get it into below, whichever helps the readability of the code to make my edits are fine.

(Note: Susan can double Respond, but Jack cannot)

(No delimiter || at the start or end of the conversation, but we can append that)

Susan: Hi how was your day?

Jack: Today was good

Susan: That's great, did you have breakfast?

Susan: Cause I had a lot to eat this morning!

Susan: It was refreshing too!

Jack: Yep I had a good breakfast today.

Susan: This conversation is closed.

Now, I have 15 - 15k such cells, each with its own unique conversations.

Which I need to extract conversational data.

-----------------------------------------------------------------------------------------------------------------

UseCases

1. Obtain the first sentence Jack says. -> Put into column B
(Jack's name will be different in each conversation. Susan will ALWAYS be Susan)

2. Find all the sentences that Susan said, that caused Jack to respond. -> Put into column C

3. Find Jack's response -> Put into column D

Then,

4. If Susan's next response to Jack is "This conversation is closed." -> Put into column E "Abandoned"

else, If Susan's next response to Jack is "Let me talk to my friend." -> Put into column E "Escalated" 

From this point onwards, I need Counts of various stuff, but I can do this myself already. I just can't seem to figure out how to do the above.

-----------------------------------------------------------------------------------------------------------------

Psuedo Code

I assume this is what I had in mind, but converting this into VBA became far too hard than I anticipated.

1. Iterate from || to || then name the string as StringIterate

2. If StringIterate contains "Susan" then StringSusan = StringIterate

3. If the Next Iteration of StringIterate does not contain "Susan" (I.e this will be StringIterate2)

Then this is Jack/user, thus StringUser = StringIterate

4. Put StringSusan into column C. Then put StringUser into column D.

5. If StringIterate2 contains "This conversation is closed." Then Put into column E "Abandoned"

else If StringIterate2 contains "Let me talk to my friend." -> Put into column E "Escalated" 

else -> Put into column E ""

Increment StringIterate = StringIterate2

Loop through all the cells in column AA in Sheet1, Input the values in Sheet2 columns B C D E respectively.

-----------------------------------------------------------------------------------------------------------------

Reason manual isn't better, thus a need for a Macro?

I get 15 - 15k conversations per day, on multiple separate CSV files. I've done a lot via help from this forum, and gotten far, but unable to figure this out.

Since I need the same text information on Sheet 2, columns B C D E, or any other way.

Five times a day EVERYDAY. A need for a macro is required.

-----------------------------------------------------------------------------------------------------------------