Using Excel - I'm tracking multiple projects and their associated assigned support resources in a worksheet and hoping to find a more automated way to sort the 20-30 projects by due date, one of several associated fields in the row (e.g., Project ID, Project Name, Start Date, End date). Immediately below that Project row are 2 or more rows naming assigned resources (e.g., Project Manager, Project Scheduler, etc. with a name assigned. Work constraints preclude my sharing an example file - but it looks something like this:
Project ID Project Name Start Date End Date
Resource Title Resource Name
Resource Title Resource Name
NEXT Project Row
The Project rows populate by XLOOKUP returns against the Project ID number. The Resource Name assignments are manually made by Supervisors when they review the coming project workload.
I'm looking for ideas for how I can automate the sort multiple Project/resource collections by the Project Start date and retain associated staff as data in the table sorts appropriately. I'm currently doing it manually - comparing Start dates against projects above or below and cutting and pasting the project and assigned resources to the correct spot to get them in date order. Awful...
Sorting by the Project Start Date will obviously put the one Project Identification row in order with the other project rows - but then the respective staffing resources are orphaned as I haven't figured out a way to associate the resourcing rows with the Project Identification row so they will "stay together" during worksheet sorting or filtering.
I'm hoping there is something "normal" excel that I just haven't come across yet - but open to VBA as an option.
Thanks in advance for any good ideas!
Max
Hi Max,
It sounds like you're not using a tabular layout ( https://www.myonlinetraininghub.com/excel-tabular-data-format) for your source data. If you were, then you could create a PivotTable report from the data that's automatically sorted and grouped as per your requirements.
In a tabular layout, you would have separate columns for each piece of information relating to a project, resulting in one row of data for each project, rather than several disassociated rows. i.e. you'd have the following columns in your table:
Project ID, Project Name, Start Date, End Date, Resource Title Resource Name 1, Resource Title Resource Name 2
If you continue to input your data in an unstructured way, then you have no choice but to use VBA or manually sort it after each change is made to the data.
I hope that points you in the right direction.
Note: if your data is sensitive, you can always share a mock-up example file with dummy data.
Mynda
