« Who Needs Google?
Feature Integration Theory »
As a follow-up to the science-fiction plot-generating spreadsheet I blogged in September, here’s a “real-world” example that came up in some consultancy I’ve been doing with my Excelsior spreadsheet generator.
The spreadsheet, and an explanation, is linked from http://www.j-paine.org/excelsior/repository/remove_non_matches/index.html. In it, column A contains a yellow table of strings; only those starting with “X” are to be put into the yellow table in column C.
Gaps are closed up, so that, for example, the second string placed in column C is one cell below the first.
There’s an intermediate table in column B, in which I calculate the positions in column A of the strings to be placed in column C. This “index table” uses INDEX and OFFSET. Essentially, its first element is the position of the first string we want to keep. Its second element is the position of the first string beyond that. And so on. The calculation is complicated by the need to handle non-matches and boundary conditions, but the basic idea is fairly simple. The key is to think in terms of recursing over tables, as though they were functions in a “normal” programming language. From discussions such as Limit a Listbox in Dick Kusleika’s Daily Dose of Excel blog, it seems doing this directly in Excel can be quite difficult.
The SF generator also used recursion, to work its way through the network of plot events.
This entry was posted
on Friday, November 16th, 2007 at 2:32 pm and is filed under Excelsior, Excel.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
