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.
-----------------------------------------------------------------------------------------------------------------
