Spy Journal 3.0 Filtering the web for you

I much prefer working in Excel 2007 to Excel 2003. Despite the issues with backward compatibility, there are a lot of advantages and benefits to using the new version.

Some little things that have been changed are

The previous limit on nested brackets in formulas from 7 has been increased to 64. I used this today

The number of columns and rows has increased. I used this today.

I had to develop a file for a client that involved a complex work roster arrangement to calculate days off in repeating 2, 3 or 4 week cycles for the next 20 years.

Here is the nested formula that got me the logic for a roster.

=IF($X5>=AJ$4,$X$4,IF($Y5>=AJ$4,$Y$4,IF($Z5>=AJ$4,$Z$4,IF($AA5>=AJ$4,$AA$4,IF($AB5>=AJ$4,$AB$4, IF($AC5>=AJ$4,$AC$4,IF($AD5>=AJ$4,$AD$4,IF($AE5>=AJ$4,$AE$4,IF($AF5>=AJ$4,$AF$4,$AG$4)))))))))

I then used one formula to generate over 600,000 cells and create a map that looks like this.

=INDEX(data,MATCH(C$1,codes,0),(IF(MOD($A3,VLOOKUP(C$1,Rules!$AH:$AI,2,FALSE))=0,VLOOKUP (C$1,Rules!$AH:$AI,2,FALSE),MOD($A3,VLOOKUP(C$1,Rules!$AH:$AI,2,FALSE))))+2)

I used the VLOOKUP formulas because this was a quick and dirty application, not one that needed to stand the test of time. Also the speed of the calculation wasn’t an issue on my development machine. I would have built more robust formulas if this application was going to see regular use.

From here I needed to generate a list of the “weekend” periods from their start date to end date.

It needed to look like the table to the right as this was going to be uploaded into their enterprise system.

Given the large number of cells this had the potential to go below the 65535 rows allowed in Excel 2003.

In the end I only used 58471 rows so was comfortably inside the limit. However if they add more rosters this could break.

I cannot display the code here as it is the intellectual property of the client – as they have paid for it.

The result for the client is huge. Entering 230,000 odd pieces of data into a system manually would have been very tedious.

Generating the 90 odd rosters manually and then uploading them would still have been very tedious and needed to be repeated if there were any problems or errors or new rosters.

This application including the initial consulting, logic development and code writing (there’s only 57 lines including all commenting)  took a little over 2 days. You do the math on how much it saved! The code runs in about 15 seconds on my system. Probably a little more on a less grunty PC. This is the power of Excel and VBA automating repetitive tasks and what we make a lot of money doing!