Hello,
I have a workbook which I need to perform a number of actions on, and I'm hoping that someone can help me please? I'm a complete noob to the Forum, but know there are geniuses out there!
CONSTRUCT
Raw data is input into Sheet1 (as in the attached workbook). I would like a macro(s) that can do the following:
- Scan column I for text that is separated by "---", as in row 34.
- If and when it finds these entries, copy the line downwards as many times as there are occurrences of "---".
- Split the text in column I based on "---" as the separator, such that there are individual text entries in each new row in column I.
- For each new line, use the ":" separator to split that note in column I into 3 columns, with headers: "Time", "Item", "Activity".
- Exception criteria: if there aren't the right number of "---" separators in column I, highlight the row so editing can be done manually - stop the macro. Manual editing would be in the form of ensuring the correct number of "---" separators are present, and then the macro would be executed again.
I'm guessing an example might be helpful. I've mentioned row 34 above; in the below, it is row 3.
AS-IS:
A B C D E F G H I J K L * * * * * * * * 2:PRODC-684:FS * * * * * * * * * * * 1:Status Meeting:Oth--- 6: PRODC-418:DEV--- 2:BOS-4927:DEV * * * * * * * * * * * 9:PRODC-418:DEV * * * * * * * * * * * 1:Meeting:Oth-- 1:Demo:QE * * *
EXCEPTION:
Macro would stop executing and highlight row 4:
A B C D E F G H I J K L * * * * * * * * 2:PRODC-684:FS * * * * * * * * * * * 1:Status Meeting:Oth--- 6: PRODC-418:DEV--- 2:BOS-4927:DEV * * * * * * * * * * * 9:PRODC-418:DEV * * * * * * * * * * * 1:Meeting:Oth-- 1:Demo:QE * * *
ROW 4 IS CORRECTED MANUALLY
A B C D E F G H I J K L * * * * * * * * 2:PRODC-684:FS * * * * * * * * * * * 1:Status Meeting:Oth--- 6: PRODC-418:DEV--- 2:BOS-4927:DEV * * * * * * * * * * * 9:PRODC-418:DEV * * * * * * * * * * * 1:Meeting:Oth--- 1:Demo:QE * * *
FINAL TO-BE
A B C D E F G H I J K L * * * * * * * * 2:PRODC-684:FS * * * * * * * * * * * 1:Status Meeting:Oth 1 Status Meeting Oth * * * * * * * * 6: PRODC-418:DEV 6 PRODC-418 DEV * * * * * * * * 2:BOS-4927:DEV 2 BOS-4927 DEV * * * * * * * * 9:PRODC-418:DEV * * * * * * * * * * * 1:Meeting:Oth 1 Meeting Oth * * * * * * * * 1:Demo:QE 1 Demo QE
Simple for the geniuses on here, right?
Your help is greatly appreciated!!
Bookmarks