Looking to extract various data points from a string of text to make a schedule.

Here's a dousy. The jist - I'm making a schedule for a fabrication shop and want to have the general "people x days" so you can ask "What's mark doing Jan 5th?" and I can tell you. But below that I want to effectively sort by project, so you can look at 'When is project 'Bridge' being worked on? or 'When is project Bridge being Welded?'

In this set up, each cell is effectively 1 day or 8 hours. This schedule is very straight forward to make with day long tasks, as you can play with index_match combos, if's, search, find, etc. The problem I am running into is when I want to be able to demonstrate 1 person working on various projects per day. What if Kevin was welding 4 different projects that each take 2 hours? What if Kevin and Mark are both working on 1 project for 2 hours each.

As such, I wanted the person x days to be my input, as there can theoretically be x number of jobs on the schedule at once and it makes more sense to me to pencil those in manually, and have the extraction from that into its respective project timeline below.

I'll try to cut to where i'm at:

Job Title: 1515
Projects: Bridge, Railing
Aspects within Projects:
Bridge - Base, Support, Mesh
Railing - corner, straight
Actions within each project: fit, weld, assemble, machine
Workers: Randy, Kevin

Fundamentally I want to be able to schedule "Kevin to fit the corner railing piece on Jan 5".

To lay this out:
I want to have 1 more lvl (1.1.1.1) where I show either workers or action. I will take whatever is easiest to implement at this point. I have shown example of by worker in 1.1.1.1 and by action in 1.2.1.1.

05-JaN______________________06-Jan ________________ 07-Jan 08-Jan 09-Jan
Randy 1515_Bridge_Base_Fit_05
Kevin 1515_Railing_Corner_Weld_02

1 1515
1.1 Bridge
1.1.1 Base
Kevin Fit_05
Randy
1.1.2 Support
1.1.3 Mesh

1.2 Railing
1.2.1 Corner
Fit weld_02
Weld
1.2.2 Straight


This is very easy to extract into "Fit_05" and "Weld_02" and currently i'm doing with search to match terms, then extract only the last part of the string. The problem is if one of those tasks is less than a day, and one person is working 2 projects.

What if it was:

Randy 1515_Bridge_Base_Fit_4, 1515_Bridge_Base_Weld_4.

Or what if 2 people were working on the base so I have the same data in 2 cells?

Without blowing up this thread from this past 40 hrs of work on this, my issues are:
If I have the subset by workers, I need to be able to return multiple actions in 1 day :
Kevin: (fit_03, weld_02,mach_04).
The only way i've found to effectively do this is with a combination of TEXTJOIN, SEARCH, FIND, to effectively find the phrase 1515_Bridge_Base in each comma delimited string, and return the last 5 digits of each of those comma delimited strings. This will blow up the function by the 5th or 6th task as i'd need to find the 5th comma to progress, for example

If I have subset by action, the problem is if 2 different people work on the same project, as id need to show 2 people in 1 cell. I can't quite get this to go because my thinking is I need to match the term 1515_Bridge_Base to an array (the column of what all workers are doing that day), and for every match, return the data from the first column (name of the worker), but return all of this to 1 cell so I can get:
Fit Kevin, Randy


I hope this wasn't the most confusing question, but basically I need to match up my project list to the schedule list, and either return all workers performing the same action on the same project on a given day (return: [Randy, Kevin]) or I need to return all the actions that are occuring for a given worker on a given project day (return: Kevin [1515_Bridge_Base_Fit_05, 1515_Bridge, Base_Weld_03]).

Tbenz,

Challenge seems ideal for two Pivot Tables, one showing the workload based on the personnel, and the other based on the project, both drawing on a single Table where each row was an employee carrying out some stage of a Project.

I'll try and put a sample together later today.

Ochimus

Ochimus,

This is very interesting and may be the right way to go through this actually. I've attached the schedule that I had brute force made up, and have made a poormans version of MS Project in some sense. But it definitely is not readily readable and doesnt allow for a super easy transition of high level detail needed on my end to be summarized to quick info for management.

With my current schedule, if you input any of the search terms located in column C into the schedule area (top 5 rows) followed by hours: SearchTermData_x.x hours and then that extrapolates into the project data below. This helps during scheduling as the schedule changes frequently but this doesnt make the information very presentable.

I want to take some of my points and mesh it with your ideas, as the issue as I see it posssibly with your information is the proper input of data into the database accounting for certain constraints (when a project can begin, when someone is available, etc).:
with my schedule I set it up so you can what dates are available as your scheduling
With your database, it's not as easy to infer things during scheduling such is 'does this user have available time? Can this project start yet?' as i'd have to input the data into the database.

So brainstorming, in some ways creating some sort of critical path analysis might actually be useful, as I could somehow extract that information into the database you have, which can then be flipped into the pivot table.