Recommendations on the use of methodology / data structure / algorithm

Question

I am looking for some recommendations on the methodology / data structure / algorithmic approach to the problem that I am trying to solve.

I am writing a special spreadsheet application in VBA. A spreadsheet is a timetable and quote document. The user enters the basic information on labor planning, which is then used to create several different worksheets / documents with the initial data presented in various formats / formats.

Honestly, Excel is not the right application for this, but this is what users want and are comfortable with, and I know VBA well, so I'm stuck.

The key data entered by the user is in the lower format, essentially, for each daily work call for each role.

╔═════╦════════╦════════════════╦════════════════╦═══════════════════╗ ║ QTY ║ ROLE ║ START ║ END ║ DESCRIPTION ║ ╠═════╬════════╬════════════════╬════════════════╬═══════════════════╣ ║ 1 ║ Rigger ║ 6/15/17 08:00a ║ 6/15/17 04:00p ║ Travel to Prep ║ ╠═════╬════════╬════════════════╬════════════════╬═══════════════════╣ ║ 1 ║ Rigger ║ 6/16/17 08:00a ║ 6/16/17 06:00p ║ Prep ║ ╠═════╬════════╬════════════════╬════════════════╬═══════════════════╣ ║ 1 ║ Rigger ║ 6/17/17 08:00a ║ 6/17/17 07:00p ║ Prep ║ ╠═════╬════════╬════════════════╬════════════════╬═══════════════════╣ ║ 3 ║ Rigger ║ 6/18/17 06:00a ║ 6/18/17 05:00p ║ Travel to Install ║ ╠═════╬════════╬════════════════╬════════════════╬═══════════════════╣ ║ 3 ║ Rigger ║ 6/19/17 08:00a ║ 6/20/17 01:00a ║ Install ║ ╠═════╬════════╬════════════════╬════════════════╬═══════════════════╣ ║ 3 ║ Rigger ║ 6/20/17 10:00a ║ 6/20/17 08:00p ║ Install ║ ╠═════╬════════╬════════════════╬════════════════╬═══════════════════╣ ║ 3 ║ Rigger ║ 6/21/17 07:00a ║ 6/21/17 04:00p ║ Travel Home ║ ╚═════╩════════╩════════════════╩════════════════╩═══════════════════╝ 

Typically, data consists of several roles over several days and often has multiple role instances on some (but not all) days.

One of the manipulations with the data that the code executes is to get this source data and reformat it into a pivot table so that the user can assign names later as soon as people are assigned. It is also the basis for various other individual work sheets and for calculating the number of flights / nights of a hotel, etc.

 ╔══════╦═══════════╦═════════╦═════════╦═══════════════════════════════════════╗ ║ NAME ║ ROLE ║ START ║ END ║ DESCRIPTION ║ ╠══════╬═══════════╬═════════╬═════════╬═══════════════════════════════════════╣ ║ ║ Rigger #1 ║ 6/15/17 ║ 6/21/17 ║ Trav | Prep | Trav | Install | Trav ║ ╠══════╬═══════════╬═════════╬═════════╬═══════════════════════════════════════╣ ║ ║ Rigger #2 ║ 6/18/17 ║ 6/21/17 ║ Trav | Install | Trav ║ ╠══════╬═══════════╬═════════╬═════════╬═══════════════════════════════════════╣ ║ ║ Rigger #3 ║ 6/18/17 ║ 6/21/17 ║ Trav | Install | Trav ║ ╠══════╬═══════════╬═════════╬═════════╬═══════════════════════════════════════╣ ║ ║ Sound ║ 6/15/17 ║ 6/22/17 ║ Trav | Install | Trav ║ ╠══════╬═══════════╬═════════╬═════════╬═══════════════════════════════════════╣ ║ ║ Crew #1 ║ 6/17/17 ║ 6/30/17 ║ Trav | Install | Show | Strike | Trav ║ ╠══════╬═══════════╬═════════╬═════════╬═══════════════════════════════════════╣ ║ ║ Crew #2 ║ 6/17/17 ║ 6/22/17 ║ Trav | Install | Trav ║ ╠══════╬═══════════╬═════════╬═════════╬═══════════════════════════════════════╣ ║ ║ Crew #2 ║ 6/26/17 ║ 6/30/17 ║ Trav | Strike | Trav ║ ╚══════╩═══════════╩═════════╩═════════╩═══════════════════════════════════════╝ 

I convert the source data from n-qty lines to nx lines from number 1 and add an instance counter to the role value if there are multiple instances. Currently, this is achieved by looping the data array several times and manipulating the data accordingly - psuedocode below

 create 2-dimensional DataArray from source data Loop DataArray generate list of unique Roles Sum all Qty values Next Create 2-dimensional OutputArray size rows to match Sum of all Qty values in DataArray size cols to match DataArray cols //determine which UniqueRoles have multiple work instances For Each UniqueRole in DataArray Loop DataArray Count unique Start Dates for UniqueRole Sum Qty values for UniqueRole Next If Sum of UniqueRole Qtys > Count of UniqueRole unique Start Dates Then Add UniqueRole to MultpleInstanceList End If Next UniqueRole //copy data into new array, expand all n-qty rows into nx rows of 1 qty Loop DataArray Do While DataArray CurrentRow Qty Value > 1 Copy DataArray CurrentRow to OutputArray NewRow Overwrite Qty value in OutputArray = 1 Reduce DataArray CurrentRow Qty value by 1 Loop Copy DataArray CurrentRow to OutputArray NewRow Next //append count to Roles with multiple instances For Each UniqueRole in MultipleInstanceList Loop OutputArray generate list of unique Start Dates for current UniqueRole Next For Each StartDate in UniqueStartDates Loop OutputArray generate row index list for matching UniqueRole AND StartDate Next initialize counter k = 1 For Each Row in RowIndexList OutputArray(Row) Role value = Role value & " #" & k k = k + 1 Next Row Next StartDate Next UniqueRoleVaue 

Then I create a pivot table from the extended array.

This works great for simple cases, however, when complex configurations exist, it can create inconsistent results when adding an instance number relative to the description value, for example ...

 ╔═════╦════════╦═════════════════╦════════════════╦═══════════════════╗ ║ QTY ║ ROLE ║ START ║ END ║ DESCRIPTION ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 1 ║ Rigger ║ 6/15/17 08:00a ║ 6/15/17 04:00p ║ Travel to Prep ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 1 ║ Rigger ║ 6/16/17 08:00a ║ 6/16/17 06:00p ║ Prep ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 1 ║ Rigger ║ 6/17/17 08:00a ║ 6/17/17 07:00p ║ Prep ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 3 ║ Rigger ║ 6/18/17 06:00a ║ 6/18/18 05:00p ║ Travel to Install ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 3 ║ Rigger ║ 6/19/17 08:00a ║ 6/19/17 06:00p ║ Install ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 1 ║ Rigger ║ 6/20/17 07:00a ║ 6/20/17 04:00p ║ Travel Home ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 2 ║ Rigger ║ 6/20/17 08:00a ║ 6/20/17 06:00p ║ Install ║ ╠═════╬════════╬═════════════════╬════════════════╬═══════════════════╣ ║ 2 ║ Rigger ║ 6/21/17 07:00a ║ 6/21/17 04:00p ║ Travel Home ║ ╚═════╩════════╩═════════════════╩════════════════╩═══════════════════╝ 

... will be displayed ...

 ╔═══════════╦═════════╦═════════╦════════════════════════════════════════════╗ ║ ROLE ║ START ║ END ║ DESCRIPTION ║ ╠═══════════╬═════════╬═════════╬════════════════════════════════════════════╣ ║ Rigger #1 ║ 6/15/17 ║ 6/21/17 ║ Trav | Prep | Trav | Install | Trav | Trav ║ ╠═══════════╬═════════╬═════════╬════════════════════════════════════════════╣ ║ Rigger #2 ║ 6/18/17 ║ 6/21/17 ║ Trav | Install | Trav ║ ╠═══════════╬═════════╬═════════╬════════════════════════════════════════════╣ ║ Rigger #3 ║ 6/18/17 ║ 6/20/17 ║ Trav | Install ║ ╚═══════════╩═════════╩═════════╩════════════════════════════════════════════╝ 

All this operation is performed several times during normal use of the document, and if the data order is changed (which is possible), it can also create inconsistent results between operations.

I would prefer not to sort the original array as part of the operation, as it is an expensive process that adds a noticeable delay after the table hits 10 rows, even when using merge sort or quick sort.

I try to make this process as optimized as possible; many other outputs use this extended array, some of which effectively provide direct feedback, so the operation is performed every time the user enters data.

The list of possible descriptions that the user can select is predefined.

 ╔═══════════════════════╗ ║ Travel to Prep ║ ╠═══════════════════════╣ ║ Travel & Prep ║ ╠═══════════════════════╣ ║ Prep ║ ╠═══════════════════════╣ ║ Prep & Travel ║ ╠═══════════════════════╣ ║ Travel to Install ║ ╠═══════════════════════╣ ║ Travel & Install ║ ╠═══════════════════════╣ ║ Install ║ ╠═══════════════════════╣ ║ Travel to Show ║ ╠═══════════════════════╣ ║ Rehearsal ║ ╠═══════════════════════╣ ║ Show ║ ╠═══════════════════════╣ ║ Show & Dismantle ║ ╠═══════════════════════╣ ║ Travel to Dismantle ║ ╠═══════════════════════╣ ║ Dismantle ║ ╠═══════════════════════╣ ║ Travel Home ║ ╠═══════════════════════╣ ║ Travel to Site Survey ║ ╠═══════════════════════╣ ║ Site Survey ║ ╠═══════════════════════╣ ║ Dark Day ║ ╚═══════════════════════╝ 

I think this is an effectively oriented graph, all edges have a direction (most of them are one-sided), and some nodes can be closed. I built an adjacency matrix for the above list, as this is apparently the logical way to determine if the assignment order is valid.

Is there an effective way to ensure that all paths for a specific role are valid workarounds and what is the best way to assign role instance numbers if one or more paths are invalid?

Or is it possible to use a subset of description values ​​at each traversal level during the extension operation to ensure that role instance numbers are correctly assigned?

Is there a certain area of ​​graph theory that I should look at? Are the graphics right? Is there an alternative approach that will work / be more effective?

Any advice / help would be greatly appreciated.

thanks

Answer 1

Focusing on the following quote:

I would prefer not to sort the original array as part of an expensive process that adds a noticeable lag once the table falls into 10 rows, even when using merge sort or quick sort.

I notice that you are referencing your source as an array. That is, users enter information into a range of sheets, and from there you use VBA to load data into an array? If so, I wonder if you know about Recordsets in VBA. From my memory, they are not quite clear for reading data, but as soon as the data is located, you can run the SQL query on it. This is not safe from performance issues, but I think you will do better than hit after 10 entries. And you probably won't have to worry about sorting methods. If you are not familiar with SQL, I would say that it is definitely worth the time, considering what you are trying to do.

Here is the guy who did it. He had another reason for this, but you could apply it in your case.

By the way, if your users are happy with Excel, then at work they can still be happy with MS Access. There is no installation of new software, you can do SQL without loading data into new objects, and you can work with forms and create reports more easily.

Answer 2

Well, deploying software integrated into an Office product, mainly Excel, is sometimes a dream for users. This does not mean that you should write the code in a spreadsheet file (.xlsm).

You have other options that could improve segregation and could easily be translated into other solutions. Some features:

1) ExcelAddIn in VBA: https://msdn.microsoft.com/en-us/library/office/gg597509%28v=office.14%29.aspx

2) ExcelAddIn in .NET: https://msdn.microsoft.com/en-us/library/cc668205.aspx

or even,

3) XLL if performance is a problem (not like your case) https://msdn.microsoft.com/en-us/library/office/bb687829.aspx

I hope this helps!