Hello.....
I am not sure if I am using the proper approach to a weekly challenge that I have to work with...I appreciate any assistance to ensure I can efficiently complete this weekly task which typically takes enormous amount of time and can be prone to mistakes.
I run a report which has over a thousand line items...For each line or row, I need to identify WO number (work order) that is referenced in one of the two column headers in the report. Unfortunately, the WO # format is not uniformly formatted as there are three types or ways they are identified. The WO's are either formatted in three ways:
1. WO w/ 9 characters: Begins with 3 letters, followed by -, followed by 5 digits (ex. DAL-76987, MDW-76855, BED-75549, etc.)
2. WO w/ 8 characters : Begins with 2 letters "CL" followed by -, followed by 5 digits (CL-615414, CL-71978, etc)
3. WO w/ 5 digits only: For example, 60511, 61400, 61563, etc. Numbers always start either with a 5, a 6 or a 7.
These WO's are referenced in the attached report in either column G "Reference" or column H "Narrative". The WO's exist in one of these columns and never in both. Most of the time, they are entered all by itself but other times, they are entered with additional narratives and can be placed in the middle of a narrative.
The challenge then is to identify the WO's and input them in a separate column, in column J "WO #". I typically sort column G first and and copy and paste them over to column J, then sort column H and do the same thing. I then clean up column J to ensure the blank spaces or extra characters that are sometimes entered before or after the WO#'s are removed. I realize this is not the most efficient way as the report is really long and I find mistakes that I missed inputting. Any ideas or better approach is appreciated. Thank you!
I am not sure if I am using the proper approach to a weekly challenge that I have to work with...I appreciate any assistance to ensure I can efficiently complete this weekly task which typically takes enormous amount of time and can be prone to mistakes.
I run a report which has over a thousand line items...For each line or row, I need to identify WO number (work order) that is referenced in one of the two column headers in the report. Unfortunately, the WO # format is not uniformly formatted as there are three types or ways they are identified. The WO's are either formatted in three ways:
1. WO w/ 9 characters: Begins with 3 letters, followed by -, followed by 5 digits (ex. DAL-76987, MDW-76855, BED-75549, etc.)
2. WO w/ 8 characters : Begins with 2 letters "CL" followed by -, followed by 5 digits (CL-615414, CL-71978, etc)
3. WO w/ 5 digits only: For example, 60511, 61400, 61563, etc. Numbers always start either with a 5, a 6 or a 7.
These WO's are referenced in the attached report in either column G "Reference" or column H "Narrative". The WO's exist in one of these columns and never in both. Most of the time, they are entered all by itself but other times, they are entered with additional narratives and can be placed in the middle of a narrative.
The challenge then is to identify the WO's and input them in a separate column, in column J "WO #". I typically sort column G first and and copy and paste them over to column J, then sort column H and do the same thing. I then clean up column J to ensure the blank spaces or extra characters that are sometimes entered before or after the WO#'s are removed. I realize this is not the most efficient way as the report is really long and I find mistakes that I missed inputting. Any ideas or better approach is appreciated. Thank you!
