Manipulating Tables

Turn Excel spreadsheets into powerful planning and scheduling software.  These simple data manipulation techniques process large tables in a systematic way.  Data processing tasks are broken into steps, with one or more steps performed on a single worksheet.

The method enables you to structure complex planning and scheduling routines into simple stages.  This brings agile software development techniques to Excel without having to learn programming code.

In an Excel-based planning system, each worksheet will typically pull in table data, perform calculations and then present the results to another worksheet or table. Much of the routine is manipulating data, applying formula and presenting it to the next process.

With normalised tables you will often have data to be joined, stacked, exploded or pivoted.  The information and calculation flow in a system is mostly made up of a series of these table operations.

Joining tables

Joining tables is equivalent to a database query. We are using a column of one table as the key to find a unique value associated with it from another table. For example, we might look up the model type for an item code in the order book by joining it with part of the item master.

Experienced users of Excel might be familiar with the VLOOKUP function. With Fast Excel, there is a better way of joining tables using INDEX and MATCH. This is a much more powerful method and results in fast, accurate table joins over hundred thousand rows of data.

Stacking tables

Stacking tables is simply combining two or more tables with the same level of uniqueness. We might wish to take order or forecasts from different regions and combine them into the same table.

Often, the format or columns are different and Fast Excel has a method that is flexible to stack data from many different table formats quickly and easily.

The Fast Excel Development Template has a automatic table stacker that can help you to combine multiple sources of data without needing complex formulas. Download the Development Template here.

Exploding tables

Exploding tables takes summary-level data and explodes each record according to its lower level relationships. The most common explosion in production scheduling is exploding sales demand against its dependent items or exploding a list of parts-to-make against the process routing for all operations required.

The Fast Excel Method uses simple formula logic to perform BOM and Routing explosions. This way enables you to take actual customer demand and calculate the exact resources required in minutes and seconds to fill customer demand. This supports powerful capacity planning functions and feeds the scheduler with the demand and process time requirement.

Next: Automating with VBA macros.